Land Rovers, Mountain Biking and being outdoors.

Filtering a Magento product collection by multiple categories

Filtering a Magento product collection by multiple categories

Well, that's a bit of a mouthful but it's also been the bane of my existence for the past few hours.  I've been building a little widget to allow product data to be embedded in a CMS page with various options for selecting the products that are included.

The difficulty I've been having is filtering a product collection by category.  Now, if you just want a single category, it's not too bad.  You can load a category collection or filter a product collection with Magento's addCategoryFilter() method.  The trouble comes when you want to find products from more than one category.

There's all sort of solutions on the web, many of them don't work on the more recent versions of Magento, others throw errors if you have products in more than one category.   After a lot of faffing about, I got this working.  I wanted a solution which didn't involve a lot of raw SQL, so hopefully it will be at least a little future-proof.   It works in Magento 1.9, so it should be OK in 1.7 and 1.8 too I'd have thought.


I'm putting on here partly so it's available to anyone else in the same position but also so I can find it easily if I need to do it again and have forgotten how :-)


First of all, we build the product collection in the normal way.  What you select and how you filter it may change of course.

$collection = Mage::getModel('catalog/product')->getCollection()
->addAttributeToFilter('status', 1)
->addAttributeToFilter('visibility', 4)

Now, in order to be able to filter against the categories, we need to join a different model.  We have a simple array of category numbers, and we first need to build the conditions for the query:

$catfilters = array(10, 20, 25);  //Obviously your category ids will be different
$conditions = array();
foreach ($catfilters as $categoryId) {
if (is_numeric($categoryId)) {
$conditions[] = "{{table}}.category_id = $categoryId";

Now we've got the conditions sorted out, we can add the syntax to join the category model and add the 'DISTINCT' operator to the collection too so that we avoid duplicates.

->joinField('category_id', 'catalog/category_product', null, 'product_id = entity_id', implode(" OR ", $conditions), 'inner');


That should be it.   You can now iterate over the collection with a foreach() or whatever you like.


Hope that helps someone :-)