Subqueries with Zend_Db

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