The other day, if was looking (and searching the web) if there was a method in Magento, with which you can easily filter a collection, in which the AND-clause for an attribute filter is within the OR-clause. In the beginning I thought this problem is quite trivial, but I finally had to realize that there is no easy way to archive that. Seanbreeden posted a solution that worked but I wanted to apply filter generated in a loop, so I had to extend that.
So in this post, I want to summarize the approach and share my learnings:
The problem:
Filter a collection (for instance the product collection) by various attributes in the following way:
|
1 2 3 4 5 |
SELECT * FROM products WHERE ( attr_a = "1" AND attr_b = "2" ) OR ( attr_a = "10" AND attr_b = "20" ); |
Simple as this nested mySql-Query, the solution in Magento is not that easy. In contrast to the query, in which the AND and OR are the other way around.
The solution:
Filter the collection with AND and OR nested the other way around, using addAttributeToFIlter(…) in Magento and then exchange AND and OR again in the where clause using string-operations.
1. Filter the collection
Get the product collection:
|
1 2 3 |
$collection = Mage::getModel('catalog/product')->getCollection(); |
Use the following filter:
|
1 2 3 4 5 6 7 8 9 10 11 |
$collection->addAttributeToFilter( array( array( 'attribute'=>'attr_a','eq'=>1 ), array( 'attribute'=>'attr_b','eq'=>2 ), )); $collection->addAttributeToFilter( array( array( 'attribute'=>'attr_a','eq'=>10 ), array( 'attribute'=>'attr_b','eq'=>20 ), )); |
That will give a mySyl query that would look like that:
|
1 2 3 4 5 |
SELECT * FROM products WHERE ( attr_a = "1" OR attr_b = "2" ) AND ( attr_a = "10" OR attr_b = "20" ); |
…so in the next step, we have to edit the where-clause programmatically.
2. Exchanging the AND and OR-Conditions in the mySQL-query
use the following code, to do that:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
// get where condition: $selectString = $collection->getSelect()->__toString(); $whereCond = substr( $selectString, strpos( strtolower($selectString) , "where" ) + 6); $oldWhereCond = $whereCond; // exchange AND- and OR-conditions and get new where condition: $whereCond = str_replace( array('AND','OR') , array('!&&!','!||!') , $whereCond ); $newWhereCond = str_replace( array('!&&!','!||!') , array('OR','AND') , $whereCond ); // reset where condition and apply new where condition: $collection->getSelect()->reset(Zend_Db_Select::WHERE); $collection->getSelect()->where($newWhereCond); |
Note that the variable $oldWhereCond is not used and it’s just for debugging. Talking about that, you could also add the following in the end, to debug the query and check the where-condition of the query:
|
1 2 3 |
$newSelectString = $collection->getSelect()->__toString(); |
2. Optional: Apply other filters
Now you can also apply other filters, for example:
|
1 2 3 |
$collection->addFieldToFilter('type_id', "simple" ); |
Note that these are added to the query with an AND-Condition, so the would query (simplified) would look like that:
|
1 2 3 4 5 6 |
SELECT * FROM products WHERE (( attr_a = "1" AND attr_b = "2" ) OR (attr_a = "10" AND attr_b = "20") ) AND (type_id = "simple"); |
3. Thats’s it
Now you can play around with the loaded products (for example in a foreach-loop):
|
1 2 3 4 5 |
foreach ($collection as $product) { // do something with $product } |
Summary:
Though I don’t really like the solution (because I think it’s very awkward, that there is no pre-built easier method in Magento), this solution is quite reliable and it works.
If you know an easier way to filter the collection, please let me know in the comments.