Subqueries with Zend_Db_Select
Relatively recently, when writing code for another model, I faced the task of using a subquery (for example, in join ). At the same time, I have been trying for as little as possible to write queries “raw”; I use the Zend Framework wrapper for this . However, looking in the API manual , to my surprise, I did not find the necessary tools. But after accessing the code directly, it became clear that a sub-query can be done very simply!
The presence of the following line in the code of the _join internal method (file Zend / Db / Select.php ) made it clear that not everything is so hopeless:
It is corny on the assumption that for $ name instanceof Zend_Db_Select some kind of its own logic is laid down, we try the following:
And as a result of the script we get:
Voila! Absolutely correct request was collected.
This is a very important feature, as gives free rein to writing more abstract patterns. For example, we create several methods that not only return some data, but requests in the form of Zend_Db_Select objects . Then these queries can be modified, built into 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 has not yet been included in the manual, PHPDoc, by which the API is received, I honestly do not understand at all.
773. } else if ($name instanceof Zend_Db_Expr || $name instanceof Zend_Db_Select) {
* This source code was highlighted with Source Code Highlighter.It is corny on the assumption that for $ name instanceof Zend_Db_Select some kind of its own logic is laid down, 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.And 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! Absolutely correct request was collected.
This is a very important feature, as gives free rein to writing more abstract patterns. For example, we create several methods that not only return some data, but requests in the form of Zend_Db_Select objects . Then these queries can be modified, built into 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 has not yet been included in the manual, PHPDoc, by which the API is received, I honestly do not understand at all.