Magento Forum

   
Add a column and filter to catalog product grid which has SEVERAL values
 
Perrine L
Jr. Member
 
Total Posts:  22
Joined:  2009-05-11
France
 

Hello,

I have created the following table in database to link one product to one or more reference(s).

+------------+------------------+------+-----+---------+----------------+
Field      Type             Null Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
ref_id     int(10unsigned NO   PRI NULL    auto_increment |
ref_text   varchar(255)     | NO   |     | NULL    |                |
ref_key    char(1)          | YES  |     | NULL    |                |
product_id int(10unsigned NO   MUL NULL    |                |
+------------+------------------+------+-----+---------+----------------+

For example, we have in this table :

+--------+-----------+---------+------------+
ref_id ref_text  ref_key product_id |
+--------+-----------+---------+------------+
|      
TOTO      G       |          |
|      
TOTO      F       |          |
|      
AZERTY    F       |         10 |
|     
10 NBBVCX    Y       |          |
|     
11 REFBLABLA F       |          |
|     
12 REFXYZ    R       |          |
|     
13 OTHERREF  NULL    |          |
|     
14 BLABLABLA NULL    |         11 |
+--------+-----------+---------+------------+

I must add a column to products grid page, which display ALL references (seperated by a coma or other) for one product on one line. Of course, the filter function must work too.

I managed to display multiple values of references, as you can see on the attached image, but I can not run the filter.

To display several values in one line, here what I did : I overloaded the class Mage_Adminhtml_Block_Catalog_Product_Grid and the method _prepareCollection() to add this “instructions” to the “getCollection” :

->joinTable('productreferences/reference',
            
'product_id=entity_id',
            array(
'ref_text'),
            
null,
            
'left')
->
addStaticField('ref_text'//useful for next "instruction"
->addExpressionAttributeToSelect(self::$COLUMN_ID_TRADE_REFERENCES,
            
'GROUP_CONCAT(ref_text SEPARATOR " ; ")',
            
'ref_text'//concat several lines
->groupByAttribute('entity_id'); //useful when we use the sql function GROUP_CONCAT

class Smile_ProductReferences_Block_Adminhtml_Catalog_Product_Grid
    
extends Mage_Adminhtml_Block_Catalog_Product_Grid
{
    
static protected $COLUMN_ID_TRADE_REFERENCES 'references';
    
    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');
            
            ;

But if I want to filter products by reference, I have an error because the following query is generated :

SELECT COUNT(DISTINCT e.entity_idFROM `catalog_product_entity` AS `e`
WHERE (GROUP_CONCAT(ref_text SEPARATOR " ; "like '&#xto;to%')
GROUP BY `e`.`entity_id`

And I don’t know how to do to change it, because my join table is not present, and the sql instruction (GROUP_CONCAT(ref_text SEPARATOR “ ; “) can’t be put in a select !!

I spend more than one day to try to make it work, but I failed :(.

That’s why I ask your help, if someone has already did that, I appreciate his/her help.

Thank you

Image Attachments
Grid product.jpg
 
Magento Community Magento Community
Magento Community
Magento Community
 
Perrine L
Jr. Member
 
Total Posts:  22
Joined:  2009-05-11
France
 

I found my mistake : I have given a bad name to my static variable : $COLUMN_ID_TRADE_REFERENCES into my class Smile_ProductReferences_Block_Adminhtml_Catalog_Product_Grid !

I have written :

static protected $COLUMN_ID_TRADE_REFERENCES 'references';

whereas I had to write :

static protected $COLUMN_ID_TRADE_REFERENCES 'ref_text';

ie the name of the field in database I want to display, in my case it is ref_text.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top