Zend_Db subquery

Question

I've been trying to construct a sql query with ZendFW, but I cant seem to get it to function like I want to (or function at all). This is the query that works that I'm trying to build with zend_db select()

SELECT tc.trip_title, td.ID, td.trip_id, 
  (SELECT count(*) FROM 'trips_invites' ti 
   WHERE ti.destination_id=td.ID AND ti.accepted ='NR') AS "pending_invites" 
FROM `trips_current` AS `tc`, `trips_data` AS `td` 
WHERE (tc.ID=td.trip_id) AND (tc.creator_id = '1') 
ORDER BY `trip_id` ASC 

What I can't figure out is how to properly get that subquery in there, and nothing I try seems to work.

Any help would be greatly appreciated!

Thanks!

Edit/Answer: If anyone will ever have a similar problem, based on the suggestion below I re-worked by query in the following way:

SELECT `tc`.`trip_title`, `td`.`ID`, `td`.`trip_id`, count(TI.ID)
FROM `trips_current` AS `tc` 
INNER JOIN `trips_data` AS `td` ON td.trip_id = tc.ID 
LEFT JOIN trips_invites AS TI ON ti.destination_id = td.id
WHERE tc.creator_id = 1  AND ti.accepted='NR'
GROUP BY td.id
ORDER BY `trip_id` ASC

which using ZendFW I created this way:

$select = $this->dblink->select() 
->from(array('tc' => 'trips_current'),
      array('trip_title'))
->join(array('td' => 'trips_data'), 
'td.trip_id = tc.id',                   
      array('ID','trip_id'))
->joinLeft(array('ti'=>'trips_invites'),
     'ti.destination_id = td.id',
     array('COUNT(ti.id)'))
->where('tc.creator_id =?',1)
->group('td.id')
->order('trip_id');

Answer 1

You don't need a subquery, you can do this with GROUP BY:

$select = $db->select()
  ->from(array("tc"=>"trips_current"), array("trip_title"))
  ->join(array("td"=>"trips_data"), "td.trip_id = tc.ID", array("ID", "trip_id"))
  ->joinLeft(array("ti"=>"trips_invites"), "ti.destination_id = td.ID", array("COUNT(*)")
  ->where("tc.creator_id = ?", 1)
  ->group(array("tc.ID", "td.ID"))
  ->order("trip_id");

I'm assuming you're using MySQL. The group-by is simpler that way because of MySQL's permissive nonstandard behavior.

edit: I change the above query to use joinLeft() for ti. This is in case no invites exist for a given destination, as you mention in your comment.

---

If you really need to use a subquery, you can create it separately and then interpolate it into the select-list of your main query:

$subquery = $db->select()
  ->from(array("ti"=>"trips_invites", "COUNT(*)")
  ->where("ti.destination_id = td.ID");

$select = $db->select()
  ->from(array("tc"=>"trips_current"), array("trip_title", "($subquery)"))
  ->join(array("td"=>"trips_data"), "td.trip_id = tc.ID", array("ID", "trip_id"))
  ->where("tc.creator_id = ?", 1)
  ->order("trip_id");

Zend_Db_Select knows to look for parentheses in the column named in your select-list and skip delimiting such columns.

Also I would like to point out that you don't have to use Zend_Db_Select just because it's there. That class is best for when you need to build up a query with parts that depend on variables or application logic. If you know the full SQL query and it doesn't depend on application conditions, it's more clear to just write it out in a string -- just like you wrote out in your original question.