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

Fruit TypeFruit Type IdTimestampId
 Apple1 12:3516
 Orange2 12:3515
 Apple1 12:2214
 Banana3 11:1913
 Apple1 11:1912
 Orange2 10:1511
 Orange2 9:2910

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.fruit_type_id
  FROM fruit f
    (SELECT 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 = 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);
  $result = $statement->execute();
  if ($result instanceof ResultInterface) {
      $resultset    = new HydratingResultSet($this->hydrator, $this->entityPrototype);
      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.

1 thought on “Sub-selects within Joins in Zend Framework 2”

  1. To do a Select inside a Join with Zend framework 2, you simply pass a Select object as a table name for you Join:

    $selectSub = new Select(‘tbluser’);
    $selectSub->do->some->stuff; // even expression like COUNT(*) as columns

    $selectMain = new Select(‘tblusersubjects’);
    $selectMain->join([‘subjoin’ => $selectSub], ‘subjoin.iserid =’, ‘*’, ‘left)

    Your sub select is now aliased with the name ‘subjoin’, so obviously use this correctly in other areas of your sql.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top