Magento: Filtering a Collection with grouped Clauses

I would like to filter a collection with grouped clauses. In SQL this would look something like:

SELECT * FROM `my_table` WHERE col1='x' AND (col2='y' OR col3='z')  

How can I “translate” this to filtering a collection with ->addFieldToFilter(...)?

One thought on “Magento: Filtering a Collection with grouped Clauses”

  1. If your collection is an EAV type then this works well:

    $collection = Mage::getResourceModel('yourmodule/model_collection')
        ->addAttributeToFilter('col1', 'x')
            array('attribute'=>'col2', 'eq'=>'y'),
            array('attribute'=>'col3', 'eq'=>'z'),

    However if you’re stuck with a flat table I don’t think addFieldToFilter works in quite the same way. One alternative is to use the select object directly.

    $collection = Mage::getResourceModel('yourmodule/model_collection')
        ->addFieldToFilter('col1', 'x');
        ->where('col2 = ?', 'y')
        ->orWhere('col3 = ?', 'z');

    But the failing of this is the order of operators. You willl get a query like SELECT * FROM my_table WHERE (col1='x') AND (col2='y') OR (col3='z'). The OR doesn’t take precedence here, to get around it means being more specific…

    $collection = Mage::getResourceModel('yourmodule/model_collection')
        ->addFieldToFilter('col1', 'x');
    $select = $collection->getSelect();
    $adapter = $select->getAdapter();
    $select->where(sprintf('(col2 = %s) OR (col3 = %s)', $adapter->quote('x'), $adapter->quote('y')));

    It is unsafe to pass values unquoted, here the adapter is being used to safely quote them.

    Finally, if col2 and col3 are actually the same, if you’re OR-ing for values within a single column, then you can use this shorthand:

    $collection = Mage::getResourceModel('yourmodule/model_collection')
        ->addFieldToFilter('col1', 'x')
        ->addFieldToFilter('col2', 'in'=>array('y', 'z'));

Leave a Reply

Your email address will not be published. Required fields are marked *