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

• mysql
• magento
• group-by

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.