ApplicationsBlog

Sub-selects within Joins in Zend Framework 2

By September 6, 2013 September 6th, 2019 One Comment

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 Type Fruit Type Id Timestamp Id
 Apple 1  12:35 16
 Orange 2  12:35 15
 Apple 1  12:22 14
 Banana 3  11:19 13
 Apple 1  11:19 12
 Orange 2  10:15 11
 Orange 2  9:29 10

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.

Caspar Harmer

Caspar Harmer

Caspar is a New Zealander working for Soliant from far-off Wellington. He loves exploring new technologies and solving problems. Caspar also loves getting into the outdoors; he runs, mountain bikes and does a lot of orienteering when he can fit it in.

One Comment

  • Avatar Christof says:

    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 = tbluser.id’, ‘*’, ‘left)

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

Leave a Reply