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');
Frequently Asked Questions
What SQL techniques does this subquery example combine? +
The example combines COUNT(), LEFT JOIN, and GROUP BY, selecting from 3 tables and counting rows from 2 different tables.
How do you embed a subquery as a selected column in a Zend_Db select? +
Wrap a nested $db->select() call inside a Zend_Db_Expr, building the subquery string with the outer table's correlated WHERE condition (e.g. c.track_id = a.id), as shown for the count_files column.
How is the LEFT JOIN with a COUNT expressed in Zend_Db? +
Use ->joinLeft(array('b'=>'track_courses'), 'a.id = b.track_id', array('count_courses' => 'COUNT(b.track_id)')) followed by ->group('a.id').