Subqueries with Zend_Db_Select

Relatively recently, when writing the code of a regular model, I was faced with the task of using a subquery (for example, in a join ). At the same time, I have been trying for quite some time to write requests “raw” as less as possible; I use for this OOP a wrapper of Zend Framework . However, having looked in the manual , API , to my surprise, I did not find the necessary funds. But after referring directly to the code, it became clear that the subquery can be made very easy!

The presence in the code of the internal _join method ( Zend / Db / Select.php file ) of the following line made it clear that not everything is so hopeless:

773. } else if ($name instanceof Zend_Db_Expr || $name instanceof Zend_Db_Select) {

* This source code was highlighted with Source Code Highlighter .

Tritely based on the fact that for $ name instanceof Zend_Db_Select some kind of logic is laid, we try the following:

$firstQuery = $db->select()
->from(array( 'u' => 'user' ),
array())
->join(array( 's2u' => 'site2user' ),
's2u.userId = u.id' ,
array( 'siteId' ))
->columns(array( 'userCount' => 'count(*)' ))
->group( 's2u.siteId' );

$secondQuery = $db->select()
->from(array( 's' => 'site' ),
array( 'siteId' => 'id' ,
'site' => 'title' ))
->join(array( 'n' => $firstQuery),
'n.siteId = s.id' ,
array( 'userCount' ));

echo $secondQuery->assemble();

* This source code was highlighted with Source Code Highlighter .

As a result of the script, we get:

SELECT `s`.`id` AS `siteId`, `s`.`title` AS `site`, `n`.`userCount` FROM `site` AS `s`
INNER JOIN ( SELECT `su`.`siteId`, count (*) AS `userCount` FROM ` user ` AS `u`
INNER JOIN `site2user` AS `su` ON s2u.userId = u.id GROUP BY `s2u`.`siteId`) AS `n` ON n.siteId = s.id

* This source code was highlighted with Source Code Highlighter .

Voila! I gathered an absolutely correct request.

This is a very important feature, because unleashes hands when writing more abstract patterns. For example, we create several methods that do not just return some data, but queries in the form of Zend_Db_Select objects. Then these requests can be modified, embedded in others, i.e. avoid writing the same SQL code in different places, and this is a big plus with the support and modification of the code.

PS This feature has been working for a long time and why it hasn’t yet been included in the manual, PHPDoc, for which the API was received, I honestly don’t understand at all.