将类别列添加到Magento管理中的产品网格 [英] Add categories column to the product grid in Magento admin

问题描述

我正在尝试将类别列添加到产品网格. 我已经修改了Mage_Adminhtml_Block_Catalog_Product_Grid. 在_prepareCollection中添加了以下内容:

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')

这给了我一个错误: a:5:{i:0;s:72:"Item (Mage_Catalog_Model_Product) with the same id "16243" already exist".

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

在prepareColumns我添加:

In prepareColumns I'm adding :

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

如何解决查询问题，使我不会收到错误消息? 是否可以按类别名称而不是ID显示和过滤?

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?

一个论坛帖子显示了类似的代码，但我无法使其适用于类别 http://www.magentocommerce.com/boards/viewthread/44534/

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');

推荐答案

我已经为这个问题工作了几天，终于解决了.由于我的解决方案只是我开发的几个产品管理网格增强功能的一部分，因此无法向您展示一个简单的剪切和粘贴解决方案.相反，我将重点放在做什么上，而不是如何上.当然，我会提供尽可能多的代码段，但我不能保证它们会独立运行.另外，请注意，我描述的解决方案仅使用Magento 1.3.2.4进行了测试.

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.

首先，您产品的category_ids属性很可能对您毫无用处.这是用逗号分隔的类别ID列表(例如206,208,231).我认为大多数人将不需要这种形式的类别. (如果这样做，您很幸运，只需将包含category_ids属性的列添加到网格中并完成操作.)另外，据我所知，Magento 1.4中不再存在此属性.

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.

此属性的问题在于，它只是实际类别分配的冗余副本.权威的类别信息存储在表catalog_category_product中，每个产品/类别对一行.

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.

由于类别是Magento中的实体，并且不能通过产品属性直接引用，因此不能将joinAttribute()或joinField()与它们一起使用.据我所知，您不能将除集合以外的所有其他类型的实体全部加入其中.

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.

但是，您可以使用joinTable()这样将类别ID放入结果集中:

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'
);

您已经发现，必须将其添加到Mage_Adminhtml_Block_Catalog_Product_Grid的_prepareCollection()函数中.与往常一样，最好的方法是创建自己的模块并添加一个扩展 Magento类的类.由于无法完全挂钩到原始的_prepareCollection()方法，因此必须将整个方法复制到覆盖的类中，然后在其中添加代码. (记住，在更新Magento时要检查原始代码是否更改.)

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.)

我们在此处使用左连接，这将导致针对具有多个类别的产品返回多个行.这基本上就是我们想要的，因为我们可以获取单个类别ID(这就是为什么我将字段称为single_category_id)并将其转换为类别名称的原因.但是，该集合不能为同一实体(即同一产品)处理多行.这可能就是您的错误消息来自的地方.

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.

现在，获取类别名称有点复杂，因为我们无法将其他实体类型加入到我们的集合中.因此，我们必须以肮脏的方式进行操作，并直接从类别实体的EAV数据库数据中删除名称.我试图保持整洁，并且不对查询中的任何属性类型ID或类似内容进行硬编码.您将需要有关 Magento的EAV结构的一些知识，以了解下面的情况.

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.

这是它的工作方式:

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

此后，$nametable将包含包含类别名称的数据库表的名称，$nameattrid将包含名称"的数字属性ID.

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".

有了这些信息，我们现在可以将正确的EAV表手动连接到查询中了:

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'
);

这将在我们的结果行中添加列single_category_name.

This will add a column single_category_name to our result rows.

请记住，每个类别的多类别产品仍然有一行.这就是我们接下来要解决的问题.为此，我们必须按产品ID对结果行进行分组，并同时将所有这些single_category_name列进行串联.

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.

分组相对容易:

$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.

连接类别名称会比较困难.由于我们是手动引入EAV表的，因此不能在类别名称属性上使用addExpressionAttributeToSelect().相反，我们必须一直深入到Zend Framework数据库类并在那里处理查询:

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 '; '), '')"
)));

这将检索基础的Zend_Db_Select并向其添加一个新的表达式列，该列将连接类别名称，并用分号-空格分隔.另外，IFNULL将处理完全没有任何类别的产品，将category_names列设置为空字符串而不是MySQL NULL值.

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 '; '), '')")));

为了显示该列，您必须在prepareColumns()中添加以下内容:

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,
));

filter和sortable标志将阻止列标题可单击，并且还将删除该列的过滤器文本框.由于我们进行了一些繁重的变通方法，因此将类别列放入网格中，因此这些功能始终无法使用.我不需要它们，因此我没有研究使它们工作起来有多困难.

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.

现在，如果您已将这两个代码块复制到安装中，您会注意到，尽管网格将正确显示结果的第一页，但在表格上方，它将说只有一个产品已退回，并且您将无法对结果进行分页.这是因为Magento使用一个单独的自动生成的SQL查询来计算结果数，并且该方法不适用于GROUP BY子句.要解决那个问题，我们必须覆盖集合类并为其添加解决方法.

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;
    }

}

getSelectCountSql()方法基于原始方法(如果未设置$calculateSizeWithoutGroupClause甚至会调用它)，但还会重置GROUP BY子句.

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.

将此新类另存为app/code/local/Our/Catalog/Model/Resource/Eav/Mysql4/Product/Collection.php(或将Our替换为您的模块名称)，并通过将app/code/local/Our/Catalog/etc/config.xml更改为包含<models>块来启用重写:

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>

最后，设置

$collection->calculateSizeWithoutGroupClause = true;

_prepareCollection()中的

将启用我们的管理网格变通方法，一切都很好.

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

这篇关于将类别列添加到Magento管理中的产品网格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！