“Zend_Db_Select” tricks
More recently, when writing code in a regular model in front of me faced with the task of the subquery (for example to join). Thus for quite some time, I try as little as possible to write queries “in the raw” form, using OOP wrapper for the Zend Framework. However, looking at the manuals, API, I, to my surprise, did not find the necessary funds. But after the treatment directly to the code, it became clear that the subquery can be very simple!
The presence of internal code method _join (file Zend / Db / Select.php) the next line, made it clear that not everything is hopeless:
773. ) Else if ($ name instanceof Zend_Db_Expr | | $ name instanceof Zend_Db_Select) (
* This source code was highlighted with Source Code Highlighter.
Banal based on the fact that for $ name instanceof Zend_Db_Select laid 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’);
$ 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! Plucked absolutely correct query.
This is a very important feature, because Untying the hands when writing more abstract models. For example, create a few methods that do not simply return any data and queries as objects Zend_Db_Select. Then these queries can be modified, embedded in the other, ie avoid writing the same SQL code in different places, and this is a big plus with the support and modify the code.
P.S. This feature has been working for quite a long time and why it has not yet been made to the manual, PHPDoc, which received API, I honestly do not understand at all.
Check out the analysis of delfi.lv, sagepub.com, widgetbox.com, flickr.com, zonamusical.net, webhost4life.com, jugem.jp, adserverplus.com, rurubu.com, home.ne.jp - and much more