Subqueries with Zend_Db

June 15, 2010
by Teo

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 , . Bookmark the permalink.

One Response to Subqueries with Zend_Db

  1. Pingback: SQL queries using Zend_Db – SELECT » DotKernel

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>