I'm building a collection for a CSV generator and I need to be able to only get products from a category and any children of it (or if I really have to, just all category ID's in an array) but from that collection, I need to then output specific names based on the category (for CSV category mapping to a 3rd party system).

I've tried countless variations but not successfully managed to achieve what I am after (so I've stripped it right back to start afresh as it was getting pretty confusing).

An example of category structure is:-

Parent Category (Ladies) - id 3
    Child Category (Trousers) - id 10
    Child Category (Shoes & Accessories) - id 11
    Child Category (Jewellery) - id 12
    Child Category (Coats & Jackets) - id 6
    Child Category (Knitwear) - id 7
    Child Category (Tops) - id 8
    Child Category (Skirts & Dresses) - id 9

So in this example, all products in any of these child categories are also in the parent category 'Ladies'.

Here is the product collection:-

// get all configurable products that are enabled and visible in search and/or catalog
$collection = Mage::getModel('catalog/product')
    ->joinField('category_id', 'catalog/category_product', 'category_id', 'product_id = entity_id', null, 'left')
    ->addAttributeToFilter('type_id', array('eq' => 'configurable'))
    ->addAttributeToFilter('visibility', array('in' => array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_SEARCH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG)))
    ->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED))
$collection->getSelect()->group('e.entity_id'); //prevents duplicate products in collection

If I load the product collection from the parent category ID however like so...

$category = Mage::getModel('catalog/category')->load(3); // 3 is category id

... then when I try to fetch category data from the collection, everything just returns information from the parent category.

What I need to do is not only build the collection from products in any of these child categories but also get the child category data that the product belongs to so I can conditionally output specific labels/names for mapping, for example:-

// build the category names for mapping
if ($category->getId()==10) {
    $cmap = 'Clothing > Trousers';

Which returns NULL of course if I load the collection by parent category (id 3).

Should I be trying to build the collection of products from the child categories and then getting the child category data or actually building a separate collection for the categories?

If I try to filter by category_id using the below for example, this doesn't seem to make any difference at all (and loads products from any categories still):-

->addAttributeToFilter('category_id', array('in' => array('6','7')))

Later in the script we loop through the products and also get the associated products so we can fetch sizes/colours etc:-

foreach ($collection as $product) {
    //get associated products (different sizes etc)
    $associatedProducts = Mage::getModel('catalog/product_type_configurable')->getUsedProducts(null,$product);

    foreach ($associatedProducts as $simple) {
        //load product
        $simple_product = Mage::getModel('catalog/product')->load($simple->getId());

        //output to CSV
        //etc etc


So to summarise, I just need to be able to load products from specific categories and then extract the child category ID (it is only the ID I require) from the collection.

  • I think I have established that ->addAttributeToFilter('category_id', array('in' => array('6','7'))) won't work unless flat catalog is disabled...? – zigojacko Jul 16 '15 at 8:10
  • Once you have the products in the parent category -3, you should be able to loop through them, load each product, and get the category information (from the configurable product) and associated simple product information that you need. – paj Jul 16 '15 at 8:15
  • Sounds simple enough but I can't for the life of me even get this pesky collection to load from the parent category any more :/ – zigojacko Jul 16 '15 at 8:30

Okay, so I'm actually getting somewhere by building a completely different collection...

$category_id = 3;
$collection = Mage::getModel('catalog/category')->load($category_id)
    ->addAttributeToSelect('*') // add all attributes - optional
    ->addAttributeToFilter('type_id', array('eq' => 'configurable'))
    ->addAttributeToFilter('visibility', array('in' => array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_SEARCH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG)))
    ->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED))

And then fetch the category ID's from the loaded products:-

$cats = $product->getCategoryIds();

And loop through (as @paj suggested in the comments) to get the category data for each:-

foreach ($cats as $category_id) {
        $category = Mage::getModel('catalog/category')->setStoreId(Mage::app()->getStore()->getId())->load($category_id);             

I'm then able to get what I want for each product like:-


Meaning my required mapping works well:-

if ($category->getId()==10) {
    $cmap = 'Clothing > Trousers';
  • I don't recommend loading categories in a loop, it results in bad performance. – Fabian Schmengler Jul 16 '15 at 9:26
  • Performance isn't critical here because this is a standalone script to generate a CSV export being run on a cron job being executed once per day outside of the Magento structure. – zigojacko Jul 16 '15 at 9:29

To get a collection of subcategories of a given category $parentCategory, use:

$subcategoryCollection = Mage::getModel('catalog/category')
    ->addFieldToFilter('level', $parentCategory->getLevel() + 1) // remove this to go deeper
    ->addFieldToFilter('path', ['like' => $parentCategory->getData('path') . '%']);

(as described here: https://stackoverflow.com/q/5564647/664108)

Unfortunately the product collection does not provide any method to filter by multiple categories at once. It would only take a little change in Mage_Catalog_Model_Resource_Product_Collection but let's not go the core rewrite way and join+filter manually instead:

$storeId = Mage::app()->getStore()->getId();
$categoryIds = $subCategoryCollection->getAllIds();
$productCollection = Mage::getModel('catalog/product')->getCollection();
$joinConditions = array(
    $productCollection->getConnection()->quoteInto('cat_index.category_id IN (?)', $categoryIds),
    $productCollection->getConnection()->quoteInto('cat_index.store_id=?', $storeId)
    array('cat_index' => $this->getTable('catalog/category_product_index')),
    array('cat_index_position' => 'position')

And then add category_id from the joined table to the result:

    array('category_id' => 'cat_index.category_id')

(this assumes that the products are only in one of the categories and not in multiple child categories or in parent and child at once)

Last but not least, add any other filters or attributes to select as needed and load the product collection.

Then you can access the category id by $product->getCategoryId()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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