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');