Add categories column to the product grid in Magento admin

Question

I'm trying to add a category column to the product grid. I've modified Mage_Adminhtml_Block_Catalog_Product_Grid. Added the following to _prepareCollection :

->joinField('category_ids',
            'catalog/category_product_index',
            'category_id',
            'product_id=entity_id',
            null,
            'left')

which gives me an error : a:5:{i:0;s:72:"Item (Mage_Catalog_Model_Product) with the same id "16243" already exist".

In prepareColumns I'm adding :

$this->addColumn('category_ids',
        array(
            'header'=> Mage::helper('catalog')->__('Categories'),
            'index' => 'category_ids',
            'width' => '150px'
    ));

How can I fix my query so I won't get the error? Is it possible to show and filter by category names instead of ids?

A forum post shows a similar code but I couldn't make it work with categories http://www.magentocommerce.com/boards/viewthread/44534/

static protected $COLUMN_ID_TRADE_REFERENCES = 'ref_text';

protected function _prepareCollection()
{
    $store = $this->_getStore();
    $collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('name')
        ->addAttributeToSelect('attribute_set_id')
        ->addAttributeToSelect('type_id')
        ->addAttributeToSelect('ref_text')
        ->joinTable('productreferences/reference',
            'product_id=entity_id',
            array('ref_text'),
            null,
            'left')
        ->joinField('qty',
            'cataloginventory/stock_item',
            'qty',
            'product_id=entity_id',
            '{{table}}.stock_id=1',
            'left')
        ->addStaticField('ref_text')
        ->addExpressionAttributeToSelect(self::$COLUMN_ID_TRADE_REFERENCES,
            'GROUP_CONCAT(ref_text SEPARATOR " ; ")',
            'ref_text')
        ->groupByAttribute('entity_id');

Answer 1

I’ve worked several days on this problem and have finally solved it. Since my solution is only one part of several product admin grid enhancements I have developed, I can’t show you a simple cut-and-paste solution. Instead, I will focus on what to do instead of how to do it. Of course I’ll provide as many code snippets as possible, but I cannot guarantee they will work on their own. Also, please note that the solution I describe has been tested with Magento 1.3.2.4 only.

First of all, the category_ids attribute of your products will most likely be of no use for you. This is a comma-separated list of category IDs (for example 206,208,231). I assume that most people will not need the categories in that form. (If you do, you’re lucky, simply add a column containing the category_ids attribute to your grid and be done.) Additionally, as far as I know this attribute does not exist anymore in Magento 1.4.

The problem with this attribute is that it is just a redundant copy of the actual category assignment. The authoritative category information is stored in the table catalog_category_product, one row per product/category pair.

Since categories are entities in Magento and are not directly referenced via a product attribute, you cannot use joinAttribute() or joinField() with them. As far as I know, you cannot join at all entities of another type than that of the collection into it.

However, you can use joinTable() to put the category IDs into the result set like this:

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

As you’ve already found out, this has to be added to Mage_Adminhtml_Block_Catalog_Product_Grid’s _prepareCollection() function. The best way to do this, as always, is to create your own module and add a class that extends the Magento class. Since there is no way to cleanly hook into the original _prepareCollection() method, you will have to copy the whole method over to your overriding class and add the code there. (Remember to check for code changes in the original when updating Magento.)

We’re using a left join here, which will lead to multiple rows being returned for products having multiple categories. This is basically what we want, since we can then take the single category IDs (that’s why I called the field single_category_id) and translate them to the category name. However, the collection cannot handle multiple rows for the same entity (i.e. the same product). That’s possibly where your error message comes from.

Now, getting the category name is a bit complicated, since we cannot join other entity types into our collection. Therefore we’ll have to do it the dirty way and directly take the names out of the EAV database data for category entities. I’ve tried to stay as clean as possible and not hard-code any attribute type IDs or the like into the query. You will need some knowledge about Magento’s EAV structure to understand what’s going on below.

Here’s how it works:

$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();

After this, $nametable will contain the name of the database table that contains the category’s name, $nameattrid will contain the numeric attribute ID for “name”.

Having this information, we can now join the correct EAV table manually into the query:

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

This will add a column single_category_name to our result rows.

Remember that we still have one row per category for multiple-category products. That’s what we’re going to fix next. To do that, we’ll have to group the resulting rows by the product ID and simultaneously concatenate all those single_category_name columns.

Grouping is relatively easy:

$collection->groupByAttribute('entity_id');

However, you should insert this before the code that joins the category name table. Don’t worry, I’ll show you a correctly sorted chunk of code at the bottom.

Concatenating the category names is somewhat harder. Since we manually brought in an EAV table, we cannot use addExpressionAttributeToSelect() on the category name attribute. Instead, we have to go down all the way to the Zend Framework database classes and manipulate the query there:

$collection->getSelect()->columns(
    array('category_names' => new Zend_Db_Expr(
        "IFNULL(GROUP_CONCAT(`$nametable`.`value` SEPARATOR '; '), '')"
)));

This retrieves the underlying Zend_Db_Select and adds a new expression column to it, which will concatenate the category names, separated by semicolon-space. Additionally, the IFNULL will take care of products not having any category at all, setting the category_names column to the empty string instead of a MySQL NULL value.

Let’s summarize it up to here:

$collection->joinTable('catalog/category_product',
    'product_id=entity_id', array('single_category_id' => 'category_id'),
    null, 'left')
    ->groupByAttribute('entity_id')
    ->joinTable($nametable,
    "entity_id=single_category_id", array('single_category_name' => 'value'),
    "attribute_id=$nameattrid", 'left')
    ->getSelect()->columns(array('category_names' => new Zend_Db_Expr("IFNULL(GROUP_CONCAT(`$nametable`.`value` SEPARATOR '; '), '')")));

In order to show the column, you have to add something like this to prepareColumns():

$this->addColumn('category_ids',
        array(
            'header'   => Mage::helper('catalog')->__('Categories'),
            'index'    => 'category_names',
            'width'    => '150px',
            'filter'   => false,
            'sortable' => false,
));

The filter and sortable flags will prevent the column header to be clickable and also remove the filter text box for that column. Since we have done some heavy workarounding to get the categories column into the grid, these features won’t work anyway. I don’t need them, therefore I have not looked into how hard it is to make them work.

Now, if you have copied these two chunks of code into your installation, you’ll note that while the grid will correctly show the first page of results, above the table it will say that only one product has been returned and you won’t be able to paginate through the results. That’s because Magento uses a separate, automatically generated SQL query to count the number of results, and this method does not work with GROUP BY clauses. To fix that, we’ll have to override the collection class and add a workaround to it.

This is a class with that workaround:

class Our_Catalog_Model_Resource_Eav_Mysql4_Product_Collection extends Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection {

    public $calculateSizeWithoutGroupClause = false;

    public function getSelectCountSql()
    {
        if (!$this->calculateSizeWithoutGroupClause) {
            return parent::getSelectCountSql();
        }
        $this->_renderFilters();
        $countSelect = clone $this->getSelect();
        $countSelect->reset(Zend_Db_Select::ORDER);
        $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
        $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
        $countSelect->reset(Zend_Db_Select::COLUMNS);
        $countSelect->reset(Zend_Db_Select::GROUP);
        $countSelect->from('', 'COUNT(DISTINCT `e`.`entity_id`)');
        return $countSelect;
    }

}

The getSelectCountSql() method is based on the original one (and even calls it if $calculateSizeWithoutGroupClause is not set), but additionally resets the GROUP BY clause.

Save this new class as app/code/local/Our/Catalog/Model/Resource/Eav/Mysql4/Product/Collection.php (or replace Our by whatever your module name is) and enable the rewrite by changing your app/code/local/Our/Catalog/etc/config.xml to contain a <models> block:

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <modules>
        <Our_Catalog>
            <version>1.2.3</version>
        </Our_Catalog>
    </modules>
    <global>
        <models>
            <catalog_resource_eav_mysql4>
                <rewrite>
                    <product_collection>Our_Catalog_Model_Resource_Eav_Mysql4_Product_Collection</product_collection>
                </rewrite>
            </catalog_resource_eav_mysql4>
        </models>
    </global>
</config>

Finally, setting

$collection->calculateSizeWithoutGroupClause = true;

in _prepareCollection() will enable our workaround for the admin grid and all is fine.

Answer 2

Per the quote above.

The filter and sortable flags will prevent the column header to be clickable and also remove the filter text box for that column. Since we have done some heavy workarounding to get the categories column into the grid, these features won’t work anyway. I don’t need them, therefore I have not looked into how hard it is to make them work.

All you need to do is paste the code below

    $collection = Mage::getModel('catalog/category')->getCollection()->addAttributeToSelect('name');
$options = array();
foreach ($collection as $item){
    if($item->getId() != ''){
    $options[$item->getId()] = $item->getName();
}
}

$this->addColumn('category_ids',
        array(
            'header'   => Mage::helper('catalog')->__('Categories'),
            'index'    => 'single_category_id',
            'width'    => '150px',
            'type' => 'options',
             'options'  => $options
));

in place of

$this->addColumn('category_ids',
    array(
        'header'   => Mage::helper('catalog')->__('Categories'),
        'index'    => 'category_names',
        'width'    => '150px',
        'filter'   => false,
        'sortable' => false,

));