Sub-selects within Joins in Zend Framework 2

Recently, I needed to get the latest version of a set of records for each category of that record.

For example, say there are three types of fruit record – Apple, Banana, Orange. Imagine that records of these types are added frequently, resulting in something like this:

Fruit Records

It turns out that this is not an easy task in Zend Framework and even in MySQL it is a little tricky.

Essentially, you need to perform a self join on the max of each record, grouped by (in this case, fruit type).

Unfortunately, you can’t actually achieve this using the Object Oriented SQL syntax that Zend Framework 2 uses natively. You need to use static sql syntax (in this case MySql) and evaluate that.

Here is the query that would be needed to achieve the above:

  $sql = 
  SELECT  f.fruit_type, f.timestamp, f.id, f.fruit_type_id
  FROM fruit f
  INNER JOIN
    (SELECT f2.id AS inner_id, f2.fruit_type_id as inner_fruit_type_id  , max(f2.timestamp) AS inner_max_timestamp
    FROM fruit f2
    GROUP BY inner_fruit_type_id)
  AS f1
  ON f.id = f1.inner_id AND f.timestamp = f1.inner_max_timestamp;

This retrieves the three records with ids of 16,15 and 13.

Now this just leaves one thing – getting Zend Framework 2 to run your raw query.

  $statement = $this->adapter->createStatement($sql);
  $statement->prepare();
  $this->adapter->getDriver()->getConnection()->connect();
  $result = $statement->execute();
  if ($result instanceof ResultInterface) {
      $resultset    = new HydratingResultSet($this->hydrator, $this->entityPrototype);
      $resultset->initialize($result);
      return $resultset;
  }
  return $result;

This assumes that you are using a Hydrator/Mapper strategy to parse your handled results. I will write about this setup in a later post.

Your returned result should now be in an easy to use HydratingResultSet format, which is easily Hydrated and Parsed.