Magento find deepest category for each product

Go To StackoverFlow.com

4

On my products grid page, I would like to display for each product the 'deepest' category in which it has been put.

This is what I've came up so far, based on some other topics about similar questions.

    $res = Mage::getSingleton('core/resource');
    $eav = Mage::getModel('eav/config');
    $nameattr = $eav->getAttribute('catalog_category', 'name');
    $nametable = $res->getTableName('catalog/category') . '_' . $nameattr->getBackendType();
    $nameattrid = $nameattr->getAttributeId();

    $collection
    ->joinTable('catalog/category_product',
    'product_id=entity_id', array('single_category_id' => 'category_id'),
    null,'left')

    ->joinTable('catalog_category_entity',
    "entity_id=single_category_id", array('cat_level' => 'level','cat_path' => 'path'),
    null,'left')

    //->addAttributeToSort('entity_id', 'ASC')
    //->addAttributeToSort('cat_level', 'ASC')
    //->groupByAttribute('entity_id')

    ->joinTable($nametable,
    "entity_id=single_category_id", array('single_category_name' => 'value'),
    "attribute_id=$nameattrid", 'left')
    ->printLogQuery(true);exit;

Which gives me the following result:

Screenshot MYSQL results

So I got my product collection and three columns are added. Clearly, for entity_id 310 'Fauteuils' is the deepest category. The only thing I have to do now is to 'group' the results by entity_id bases on the highest cat_level. However, 'group by' does not give me the desired result.

Thanks

2012-04-04 08:15
by Erwin Otten


4

Here is a simple solution that you can take ideas from:

$category  = $product->getCategory();
$path      = explode('/', $category->getPath());
$deepestId = $path[count($path) - 1];

$deepestCategory = Mage::getModel('catalog/category')->load($deepestId);

Zend_Debug::dump($deepestCategory->getData());exit;

You'll want to look at the path column in the category table, and then find the last category ID in the path.

2012-04-04 13:57
by Zachary Schuessler
Thanks, but I've got no problem getting the path or the category level as you can see in my progress so far (and screenshot). Also, loading each category wouldn't be very smart performance wise - Erwin Otten 2012-04-04 19:56
It totally worked for me. This is a very nice solution - RPDeshaies 2014-03-20 13:57
Ads