I have a custom table with a product reference product_id. Now I would like to show product information (sku, name) in my backend grid, but I am unsure what is the best practice to do this?

My best guess for SKU is as follows:

$collection->join(
    'catalog/product',
    'product_id=`catalog/product`.entity_id',
    array('product_sku' => 'sku')
)

(code from the _prepareCollection() method in my grid block class)

But what about the product name? It can be found in catalog_product_entity_varchar. My understanding is that you can rather easy get it if your own resource model and collection is based on Mage_Eav_Model_Entity_Collection_Abstract because then you can use methods like joinAttribute. But my model is based on a simple table and extending from Mage_Core_Model_Resource_Db_Collection_Abstractand there is no joinAttribute method available.

So what is the best way to get the product name in this case?

Thanks for your time and help :-)

Update: To be more precise, I was talking about my resource model and collection. It matches a simple flat table with just a few attributes like

entity_id    product_id    created_at    user_id

My intention is to grid in the backend where I show some statistics:

ProductSku    Count(ProductSku)    MAX(created_at)

As far as I know, the best approch to do this, is via the grid block class and the method to go is _prepareCollection().

My method looks like this:

protected function _prepareCollection()
{
    // Get and set our collection for the grid
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $collection
        ->join(
            'catalog/product',
            'product_id=`catalog/product`.entity_id',
            array('product_sku' => 'sku')
            )
        ->addExpressionFieldToSelect('product_count', 'COUNT({{product_id}})', 'product_id')
        ->addExpressionFieldToSelect('newest', 'MAX({{created_at}})', array('created_at'=>'main_table.created_at'))
        ->getSelect()->group('product_id');
    $this->setCollection($collection);

    return parent::_prepareCollection();
}

This works well for the sku (which I refer to as product_sku in the _prepareColums() method. But what join do I need to insert here in order to get the name (and e.g. the manufacturer)?

Am I doing something wrong because I can't use joinLeft()?

up vote 13 down vote accepted

In your collection class (/Some/Module/Model/Mysql4 (or Resource)/YourModel/Collection.php) add this method:

public function addProductData()
    {
        /** add particular attribute code to this array */
        $productAttributes = array('name', 'price', 'url_key');
        foreach ($productAttributes as $attributeCode) {
            $alias     = $attributeCode . '_table';
            $attribute = Mage::getSingleton('eav/config')
                ->getAttribute(Mage_Catalog_Model_Product::ENTITY, $attributeCode);

            /** Adding eav attribute value */
            $this->getSelect()->join(
                array($alias => $attribute->getBackendTable()),
                "main_table.product_id = $alias.entity_id AND $alias.attribute_id={$attribute->getId()}",
                array($attributeCode => 'value')
            );
            $this->_map['fields'][$attributeCode] = 'value';
        }
        /** adding catalog_product_entity table fields */
        $this->join(
            'catalog/product',
            'product_id=`catalog/product`.entity_id',
            array('sku' => 'sku', 'type_id' => 'type_id')
        );
        $this->_map['fields']['sku']     = 'sku';
        $this->_map['fields']['type_id'] = 'type_id';
        return $this;
    }

In your grid block use this function:

 protected function _prepareCollection()
    {
        $collection = Mage::getModel('some/yourmodel')
            ->getCollection()->addProductData();
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }
  • This looks promising, but it does not work unfortunately. My Collection extends from Mage_Core_Model_Resource_Db_Collection_Abstract and I get an error Call to undefined method Mycompany_Module_Model_Resource_Mymodel_Collection::joinLeft(). I guess this is because I don't use an EAV Resource Model? – Celldweller Oct 31 '13 at 16:59
  • Use join instead of joinLeft, I edited the answer – mageUz Oct 31 '13 at 17:21
  • doh! I feel stupid now! ;-) And I don't know why I tried it. Thank you very much! – Celldweller Oct 31 '13 at 17:40
  • @Celldweller, you don't even absolutely need to use $this->_map['fields']['sku'] = 'sku' or similar in this case. You need it only if you have several identical fields name and need to do a translation to prevent conflicts. It just adding overhead for this use case. In a different use case, you may use $this->_map['fields']['my_sku'] = 'sku' then you can use with the collection and _prepareColumns: $this->addColumn('my_sku', array(…)), it will help you to prevent conflict for the filtering or columns sorting if you have a field sku used in different DB tables common to the collection – Sylvain Rayé Nov 1 '13 at 8:49
  • Great answer! It may lead to "item with the same ID already exists" issues though. A simple $this->getSelect()->group('main_table.product_id'); solves the issue, but maybe the code can be optimised, so that duplicates are not created in the first place? – Simon Sep 6 '16 at 6:33

Hi Celldweller I hope you're doing well :-)

Maybe you were wrong in your explanation about the class Mage_Core_Model_Resource_Db_Collection_Abstract, you extend a resource collection not the model because you must not extend a model with a collection class if you want to respect the Magento structure. Am I right?

Based on my correction, I see different kind of approach, depending on how often you want to get the product name attribute. In any case, I think to do a sql query via the Magento Framework is the best way and efficient. It's more faster than to do a Mage::getModel('catalog/product')->load(1234)->getName() for each loaded item. So in fact it will be very similar to the code you use for the sku

CODE NOT TESTED

You want those information each time a collection is loaded

You may in your collection class set into a _beforeLoad method such a code:

protected function _beforeLoad()
{
    $productName = Mage::getSingleton('eav/config')->getAttribute('catalog_product','name');

    $this->getSelect()
        ->join( array('product_attribute' => $productName->getBackendTable()),
            'main_table.product_id = product_attribute.entity_id',
            array())
        ->where("product_attribute.attribute_id = ?", $productName->getId());
    }

    return parent::_beforeLoad();
}

You want those information ONLY for the grid

In your _prepareCollection, you will have to add a method in your collection with the same code as it was done above into the _beforeLoad then you can prepare the collection by using this method. Don't use both, I mean don't use together the same code in _beforeLoad and addProductName methods, use only one of them. Here is a sample:

Into your grid.php:

protected function _prepareCollection()
{
    ...
    $collection->addProductName();
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

Into your Collection.php:

public function addProductName()
{
    $productName = Mage::getSingleton('eav/config')->getAttribute('catalog_product','name');

    $this->getSelect()
        -> join( array('product_attribute' => $productName->getBackendTable()),
            'main_table.product_id = product_attribute.entity_id',
            array())
        ->where("product_attribute.attribute_id = ?", $productName->getId());

    return $this;
}
  • Great to read you again :-) Thank you very much but I can't use joinLeft() method. I guess it is a EAV resource model-only method? You were right, I was unprecise in my initial question. I will edit it :) – Celldweller Oct 31 '13 at 17:02
  • Oh and btw: Of course I don't want to do a product->load() in such a situation. Shoot me if I ever would dare! ;-) My goal is to have it all in one collection in order to be able to do sorting and filtering in the grid. See the update in my initial post for further details. – Celldweller Oct 31 '13 at 17:19
  • So replace joinLeft by join but you already accepted the other answer although I answered correctly and was the first one to answer :-( – Sylvain Rayé Nov 1 '13 at 7:23
  • Yes, you are right. I was so happy that it finally worked, that I didn't think about it. I am very thankful to both of you. Hope you can forgive me! I owe you a beer. – Celldweller Nov 1 '13 at 8:47
  • ahah no worry. We meet you in Berlin with pleasure :-) – Sylvain Rayé Nov 1 '13 at 8:50

I had almost the same problem, but i can't add a comment, because I not have 50 reputation. I spent a lot of time trying to figure out what is wrong (I used code of Sylvain Rayé). My product collection for some reason filtered. So I found a reason.

If you using some import tools (magmi, etc), they often do not create empty attributes at once. Therefore using ->where("product_attribute.attribute_id = ?", $productName->getId()) products that do not have this attribute, will disappear from selection.

Right way is using joinLeft like that:

$productName = Mage::getSingleton('eav/config')->getAttribute('catalog_product','name');

$this->getSelect()
     ->joinLeft(array('product_attribute' => $productName->getBackendTable()),
        "main_table.product_id = product_attribute.entity_id AND
         product_attribute.attribute_id = {$productName->getId()}",
        array());

Hope this helps someone.

Display custom attribute on product grid.

Please overwrite this block Mage_Adminhtml_Block_Catalog_Product_Grid in your extension and copy functions _prepareCollection and _prepareColumns into your extension's block file.

Add below code to select attribuet in _prepareCollection function of product grid Mage_Adminhtml_Block_Catalog_Product_Grid before $this->setCollection($collection) line.

$attributeCode = 'qc_status';//here your attribute code
        $collection->joinAttribute($attributeCode, 'catalog_product/'.$attributeCode, 'entity_id', null, 'left');
        $collection->addAttributeToSelect($attributeCode);

And then below code for column in _prepareColumns function of grid.

$attributeCodeConfig ='qc_status';//Your attribute code...

        $attributeId = Mage::getResourceModel('eav/entity_attribute')->getIdByCode('catalog_product', $attributeCodeConfig);

        $attribute = Mage::getModel('catalog/resource_eav_attribute')->load($attributeId);
        $attributeData = $attribute->getData();
        $frontEndLabel = $attributeData['frontend_label'];

        $attributeOptions = $attribute->getSource()->getAllOptions();
        $b = new Mage_Catalog_Model_Resource_Eav_Attribute();
        $attributeOptions2 = array();
        foreach ($attributeOptions as $value) {
            if(!empty($value['value'])) {
                $attributeOptions2[$value['value']] = $value['label'];
            }

        }


        if(count($attributeOptions2) > 0) {
            $this->addColumn($attributeCodeConfig,
                array(
                    'header'=> Mage::helper('catalog')->__($frontEndLabel),
                    'width' => '80px',
                    'index' => $attributeCodeConfig,
                    'type'  => 'options',
                    'options' => $attributeOptions2,

            ));
        } else {
            $this->addColumn($attributeCodeConfig,
                array(
                    'header'=> Mage::helper('catalog')->__($frontEndLabel),
                    'width' => '80px',
                    'index' => $attributeCodeConfig,

            ));
        }
  • Change core files is no good practice. Same for new SomeModel() – sv3n Feb 8 at 12:25
  • we don't have to changed in core file .we can overwrite that block (Mage_Adminhtml_Block_Catalog_Product_Grid ) to accomplish our functionality – Savoo Feb 9 at 5:51

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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