Magento filter collection – nested AND- and OR-clauses

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:

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:

Use the following filter:

That will give a mySyl query that would look like that:

…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:

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:

2. Optional: Apply other filters

Now you can also apply other filters, for example:

Note that these are added to the query with an AND-Condition, so the would query (simplified) would look like that:

3. Thats’s it

Now you can play around with the loaded products (for example in a foreach-loop):


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.


Kommentieren Sie den Artikel