How to display EAV attribute data in Magento custom grid?

By: Chandresh Rana, Aug 17th, 2018 # How to # Magento

It’s tough when you’re dealing with Magento 1 custom grid view and you have to display EAV attribute data in grid. Right? After tweaking the minds for hours, we at Aureate Labs would like to present a best and easy go solution that can work without a single error. Let’s make it happen in a couple of minutes, In a very short journey before we jump on a actual code, make sure you’ve needed table structure same as displayed below: First, create two tables related to the testimonial. 1.  testimonial table (parent table): stores All testimonial data. 2.  testimonial_product_customer (child table): stores Testimonial_id (FK), Product_id(FK), Customer_ID(FK) relational data. Here is the detailed table structure with required columns: Testimonial
  • Id (Primary key)
  • company_name
  • name
  • message
  • post
  • profile_pic
  • status
  • created_at
  • Updated_at
Testimonial_product_Customer
  • Id (Primary key)
  • testimonial_id (Foreign key - testimonial)
  • product_id (Foreign key - product)
  • customer_id (Foreign key - customer)
Now, all we need is to display these products and customers related data in testimonial grid without breaking any functionality of searching or sorting the data. And that can be easily done with the help of LEFT JOIN (EAV TABLES). Coming to code, we have to update _prepareCollection() method of testimonial grid block class (Grid.php) placed at  app/code/local/{Namespace}/Testimonials/Block/Adminhtml/Testimonials/
protected function _prepareCollection()
{
    $collection = Mage::getModel('testimonials/testimonial')->getCollection();
    $productsTableName = Mage::getSingleton('core/resource')->getTableName('catalog/product');
    $productCustomerTableName = Mage::getSingleton('core/resource')->getTableName('testimonials/testimonial_product_customer');
    $entityTypeId = Mage::getModel('eav/entity')
        ->setType('catalog_product')
        ->getTypeId();
    $entityCustTypeId = Mage::getModel('eav/entity')
        ->setType('customer')
        ->getTypeId();
    $prodNameAttrId = Mage::getModel('eav/entity_attribute')
        ->loadByCode($entityTypeId, 'name')
        ->getAttributeId();
    $custNameAttrId = Mage::getModel('eav/entity_attribute')
        ->loadByCode($entityCustTypeId, 'firstname')
        ->getAttributeId();
    $collection->getSelect()
        ->joinLeft(
            array('testproduct' => $productCustomerTableName),
            'main_table.id = testproduct.testimonial_id',
            array('product_id','testimonial_id','customer_id')
        )
        ->joinLeft(
            array('prod' => 'catalog_product_entity'),
            'prod.entity_id = testproduct.product_id',
            array('sku')
        )
        ->joinLeft(
            array('cpev' => 'catalog_product_entity_varchar'),
            'cpev.entity_id=prod.entity_id AND cpev.attribute_id='.$prodNameAttrId.'',
            array('cpev.value' => 'value')
        )
        ->joinLeft(
            array('ccev' => 'customer_entity_varchar'),
            'ccev.entity_id=testprodcust.customer_id AND ccev.attribute_id='.$custNameAttrId.'',
            array('ccev.value' => 'value')
        );
    $this->setCollection($collection);
    return parent::_prepareCollection();
}
Next, to display product and customer related columns in grid view,  add them to the method_prepareColumns as defined below.
protected function _prepareColumns()
{
    $this->addColumn('pro_name', array(
        'header'    => Mage::helper('testimonials')->__('Product'),
        'align'     =>'right',
        'index'     => 'cpev.value',
        'width'     => '50px',
    ));
    $this->addColumn('cust_name', array(
        'header'    =>Mage::helper('testimonials')->__('Customer'),
        'align'     =>'right',
        'index'     => 'ccev.value',
        'width'     => '50px',
    ));
    return parent::_prepareColumns();
}
That is all! Now you can check for the EAV attributes data displayed successfully in the custom grid. If you encountered with any error, don’t hesitate to connect us. We will be glad to help you.