How do I get products that are in any sub-category AND category b, ignoring all other products that are in one or the other?

$cats = explode(',', $_category->getChildren()); // all sub-category products // array(4,5,6)
$cats[] = 15; // Specific brand category //$cats = array(4,5,6,15)
$bottomRangeCollection = Mage::getModel('catalog/product')->getCollection()
                                 ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left outer')
                                 ->addAttributeToFilter('category_id', array('in' => array('finset' => $cats)))

But this is returning all products in all categories.

How do I filter the products to categories 4 and 15, or 5 and 15, or 6 and 15.


Hacky solution:

Remove the specific brand category id from the $cats array, so we just get all products from the sub-categories, then we do the below - a foreach that adds products that are in the brand category to an array.

//if product is also in brand category, add it to our little array
$rangeProducts = array();
foreach($bottomRangeCollection as $product) {
    if(in_array(15, $product->getCategoryIds())) {
        $rangeProducts[] = $product;
unset($bottomRangeCollection); //throw out the garbage.

There has to be a way to do this in Magento.


I have the following category structure:


Products will be put into a relevant category (such as Sinks) and into a Range category (such as Deor). What I am trying to do, is get the range products (the Deor products in this example) that are also in the Bathroom sub-categories (both Sinks and Cabinets), BUT ignoring all other range products, and any products in the bathroom sub-categories that is not in a Range category (which shouldn't be any. But, you know, covering all bases).

  • Let me see if I understood your question... you have a category b and you want to get all the products that are occuring in minimum 2 subcategories from the parent category. right ? Commented Feb 20, 2016 at 10:13
  • Hi @AlexConstantinescu I have updated my question. Does it help or do we need to meet in chat to discuss? Commented Feb 21, 2016 at 21:15

1 Answer 1


It works if you move the filter to the join condition. In your special case you need two separate joins, one to match the products in your subcategories and one to match the special brand category.

$cats = explode(',', $_category->getChildren());
brandCategoryId = 15;

$bottomRangeCollection = Mage::getModel('catalog/product')->getCollection()
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', "at_category_id.category_id IN ($cats)", 'right')
    ->joinField('brand_category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', "at_brand_category_id.category_id = $brandCategoryId", 'right')

Note that the join conditions are raw SQL, there is no quoting involved. If you can not absolutely be sure that the category id variables are just numbers, use $connection->quoteInto() to escape the values.

  • I had to change the join type from left to right otherwise it returned all the products. Commented Feb 22, 2016 at 9:27
  • Makes sense, thanks! I updated the answer accordingly Commented Feb 22, 2016 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.