2

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?

4

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

  • Does this break later attempts to use addFieldToFilter? – user2045 Jul 1 '13 at 20:38
  • 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 Jul 1 '13 at 20:48
  • It's tough to show some really great examples because they're in Enterprise Edition... – philwinkle Jul 1 '13 at 20:49
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.