Subquery Using Magento's DB Functions

Question

I am trying to do a query of the form:

SELECT TableA.id, TableB.id 
FROM TableA
INNER JOIN TableB ON TableB.a_id = TableA.id 
WHERE
TableB.field1 = ( SELECT TableB.field1 FROM TableB WHERE TableB.a_id=TableA.id ORDER BY TableB.created_at DESC LIMIT 1 )
GROUP BY TableA.id, TableB.field1 ORDER BY TableA.id ASC

the important thing being that the where clause has a subquery.

My question is, how would you use addFieldToFilter() or even ZendDB functions to implement the subquery?

Answer 1

You would use Zend_Db_Expr - which can take either another collection object, an explicit SQL statement, or a fragment of a statement. :

$collection->getSelect()->where('TableB.field1 = (?)', new Zend_Db_Expr($subquery->__toString());

Here, $subquery is another collection but could easily just be the text as you stated, SELECT TableB.field1 FROM TableB WHERE TableB.a_id=TableA.id ORDER BY TableB.created_at DESC LIMIT 1

• user2045: Does this break later attempts to use addFieldToFilter?
• philwinkle: Not in my experience, no. In core they do these calls successively -- I've seen Zend_Db_Expr used in column definition, e.g. in the SELECT part of the query.
• philwinkle: It's tough to show some really great examples because they're in Enterprise Edition...

Answer 2

You can check this example in the magento source code: Mage_Sales_Model_Resource_Report_Bestsellers::aggregate

search for 'subSelect': http://www.magentodocs.org/1.7.0.2/d1/d51/_sales_2_model_2_resource_2_report_2_bestsellers_8php_source.php

More examples you can find in the Mage_Sales_Model_Resource_Report_* classes.