zend_db obtain "select ..., (select ...) as x from ... "

Question

I have a query that i can't build with Zend_Db_Select

SELECT `f`.*,          
        (SELECT Sum(x) AS `y`
        FROM   z AS pf
        WHERE  pf.q_id = f.id) AS w
FROM  f ...
WHERE ...
GROUP  BY `f`.`id`  

so at the moment i'm running it manually $db->fetchAll($sql).

How do i obtain

select f.* , (select ...) as `something` from ...

I was thinking using ->column('f.*, (select...)') but it didn't work,

it could work maybe with a left join if i do (select ..., id) and then join on that id, but i wanted to obtain THIS very sql query. Is it possible?

thanks

Answer 1

I would recommend the JOIN. You might get better performance as sub selects are usually hard for the database to optimize. It is also easy to write this with Zend_Db_Select. Alternately new Zend_Db_Expr might work for this.

   $select = $db->select()
   ->from('f', array('f.foo', 'f.bar', new Zend_Db_Expr('SELECT Sum(x) AS `y`
                                                         FROM   z AS pf
                                                         WHERE  pf.q_id = f.id') => 'f'))
   ->where(...);