Nested queries using Zend_Db_Select

recently when writing code, another model arose for me to use the subquery (for example, in join). In this case for quite some time I like to write the queries "cheese"; use OOP wrapper Zend Framework. However, by looking in the manual, API, I, to my surprise I did not find the necessary funds. But after appealing directly to the code, it became clear that a subquery can be done very simply!

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

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

/ > * This source code was highlighted with Source Code Highlighter.

Corny on the basis of what for $name instanceof Zend_Db_Select contain some logic, 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');

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

echo $generic- > assemble();

* This source code was highlighted with Source Code Highlighter.

And the 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! Gathered absolutely correct query.

This is a very important feature, because a free hand in writing a more abstract models. For example, create some methods that do not simply return some data, and queries as objects Zend_Db_Select. Then these queries can be modified, to incorporate in the other, i.e., to avoid writing the same SQL code in different places, and this is a big plus with the support and code modifications.

PS This feature has been working for quite a long time and why it still has not made a manual, PHPDoc, which obtained the API, I honestly don't understand at all.

Article based on information from habrahabr.ru