How to pass a complete SQL query to the model?

Question

This is v1.4.x to be clear.

I have recently found a limitation in the Magento-generated query for products, cited in this thread:

adding addFieldsToFilter() does INNER JOIN on eav tables, removing all products which don't have that attribute

On that subject, I started searching for a way to pass a complete SQL query to the model, but have not found any way listed. Is this possible? Again, this is a limited case and Magento unfortunately does an INNER JOIN with no remedy when I need a LEFT JOIN.

Answer 1

I'll answer this for future readers, ignoring the fact that you don't need it anymore because there is a simple solution to your original problem.

You can modify the Zend_Db_Select instance for any collection, which means that you can effectively change the query to anything.

Here is a simple and stupid example:

$collection->getSelect()->reset(Zend_Db_Select::WHERE)->where('foo=bar');

But be aware that many collections make their own changes in the _beforeSave() method or using an observer.