Filtering a Magento product collection by multiple categories

Photo of post

We’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 comes with 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. We 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.


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.

Code snippets on this page are covered by the MIT licence