Magento pull product attributes into Adminhtml order item grid

Magento pull product attributes into Adminhtml order item grid

October 4, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Magento pull product attributes into Adminhtml order item grid

Extending Magento Adminhtml Grid: Pulling Product Attributes

Hello Mark,

I understand your requirement to add product attributes like brand and flavor as additional columns to your existing order item grid in the admin panel of a Magento site. With flat indexing turned on, joining tables directly might be challenging.

Joining EAV Tables for Product Attributes

You’re on the right track by attempting to join the catalog_product_index_eav table with the eav_attribute_option_value table using the product ID (entity_id). Since you’re dealing with EAV (Entity-Attribute-Value) objects, joining these tables is necessary.

Here’s how you can modify your collection setup:

$collection = Mage::getModel('sales/order_item')->getCollection()
    ->addFieldToFilter('order_id', $orderId)
    ->join(
        'catalog_product_index_eav',
        '`catalog_product_index_eav`.`entity_id` = `main_table`.`product_id` AND `catalog_product_index_eav`.`attribute_id` = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = "brand")',
        array('attribute_id')
    )
    ->join(
        'eav_attribute_option_value',
        '`eav_attribute_option_value`.`option_id` = `catalog_product_index_eav`.`value`',
        array('brand' => 'value')
    );

This code joins the order item collection with the EAV index and option value tables using the product ID as the linking field. It filters for the specific brand attribute.

Adding New Columns to Adminhtml Grid

To add this new brand column to your grid, you’ll need to modify your _prepareCollection function:

protected function _prepareCollection()
{
    $collection = Mage::getSingleton('sales/order_item')->getCollection();
    $this->setCollection($collection);

    $columns = array(
        // ... existing columns ...
        'brand' => array(
            'header'=> Mage::helper('sales')->__('Brand'),
            'index' => 'brand',
        ),
    );

    $this->setCollection($collection);
    parent::_prepareCollection();

    return parent::_prepareCollection();
}

In the _prepareColumn function, add a new entry for brand:

protected function _prepareColumns()
{
    if (!$this->getCollection()->isLoaded()) {
        $this->getCollection()->load();
    }

    $columns = array(
        // ... existing columns ...
        'brand' => array(
            'header'=> Mage::helper('sales')->__('Brand'),
            'index' => 'brand',
        ),
    );

    return $columns;
}

Solving the Column Not Found Error

The error Unknown column 'brand' in 'where clause' occurs because you’re trying to use an alias (brand) in a WHERE clause, which is not allowed by MySQL. To resolve this:

  1. Add the brand attribute as a selectable field in your initial collection setup:
$collection = Mage::getModel('sales/order_item')->getCollection()
    ->addFieldToFilter('order_id', $orderId)
    ->join(
        'catalog_product_index_eav',
        '`catalog_product_index_eav`.`entity_id` = `main_table`.`product_id` AND `catalog_product_index_eav`.`attribute_id` = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = "brand")',
        array('attribute_id', 'brand')
    )
    ->join(
        'eav_attribute_option_value',
        '`eav_attribute_option_value`.`option_id` = `catalog_product_index_eav`.`value`',
        array()
    );
  1. Make sure the brand field is used in your grid’s _prepareCollection and _prepareColumns functions with the correct index.

Remember to clear your Magento cache after making these changes for them to take effect.

Conclusion

Adding custom columns to the Adminhtml grid in a Magento site requires modifying the collection setup and grid rendering functions. By joining the EAV tables properly and defining the new column in your grid configuration, you can successfully pull product attributes like brand into your order item grid.

For more detailed tutorials on extending Magento grids, I recommend checking out Laravel Company’s Magento development guides.

Catalog Indexing Database Admin

Our network