Continuing the Zend_DB article series, we are stopping now at subqueries.
As you note, the below is a complicate query, with COUNT(), LEFT JOIN(), GROUP BY
– select from 3 tables, and make a count from 2 different tables:
SELECT a.id, a.title, (SELECT COUNT(c.track_id) FROM track_files AS c WHERE c.track_id = a.id ) AS `count_files`, COUNT(b.track_id) AS count_courses FROM tracks AS a LEFT JOIN track_courses AS b ON (a.id = b.track_id) GROUP BY a.id |
Initialize the connection to our MySql database:
$db = Zend_Db::factory('Pdo_Mysql', $dbConnect); |
$db->select() ->from(array('a'=>'tracks'), array('id', 'title', 'count_files' => new Zend_Db_Expr( '('.$db->select() ->from(array('c'=>'track_files'), array(new Zend_Db_Expr('COUNT(c.track_id)'))) ->where('c.track_id = a.id').')' ) ) ) ->joinLeft(array('b'=>'track_courses'), 'a.id = b.track_id', array('count_courses' => 'COUNT(b.track_id)') ) ->group('a.id'); |
This entry was posted in Best Practice and tagged Zend Framework, Zend_Db. Bookmark the permalink.
Pingback: SQL queries using Zend_Db – SELECT » DotKernel