Magento 1.9 – Add Two Attribute Values and Display Result as Column in Admin Grid

gridmagento-1.9

I have an admin grid with two attributes of customer (attr1 and attr2). I can display them in the grid as two separate columns with addAttributeToSelect method.

protected function _prepareCollection() {
    $collection = Mage::getResourceModel('customer/customer_collection')
        ->addAttributeToSelect('email')
        ->addAttributeToSelect('attr1', 'left')
        ->addAttributeToSelect('attr2', 'left');
    ...

I need to add a third column displaying the sum of the values of those attributes. I can display this column too, with addExpressionFieldToSelect.

    ...
        ->addExpressionAttributeToSelect('sum', 'SUM({{attr1}})+SUM({{attr2}})', array(
            'attr1', 'attr2'
        );

    $collection->getSelect()->group('e.email');
}

The problem is if I filter the sum column, Magento gives me an error:

SQLSTATE[HY000]: General error: 1111 Invalid use of group function, query was: SELECT COUNT(*) FROM `customer_entity` AS `e`
 LEFT JOIN `customer_entity_int` AS `at_attr1` ON (`at_attr1`.`entity_id` = `e`.`entity_id`) AND (`at_attr1`.`attribute_id` = '866')
 LEFT JOIN `customer_entity_int` AS `at_attr2` ON (`at_attr2`.`entity_id` = `e`.`entity_id`) AND (`at_attr2`.`attribute_id` = '864')
 WHERE (`e`.`entity_type_id` = '1') AND (SUM(at_attr1.value)+SUM(at_attr2.value) >= '0.000000') GROUP BY `e`.`email`

I tried to define a custom filter callback:

protected function _prepareColumns() {
    $this->addColumn('email', array(
        'header' => Mage::helper('customer')->__('Email'),
        'width' => '300px',
        'index' => 'email'
    ));
    $this->addColumn('attr1', array(
        'header' => Mage::helper('core')->__('Attr 1'),
        'index' => 'attr1',
        'type' => 'price',
        'currency_code' => $store->getBaseCurrency()->getCode(),
    ));
    $this->addColumn('attr2', array(
        'header' => Mage::helper('core')->__('Attr 2'),
        'index' => 'attr2',
        'type' => 'price',
        'currency_code' => $store->getBaseCurrency()->getCode(),
    ));
    $this->addColumn('sum', array(
        'header' => Mage::helper('core')->__('SUM'),
        'index' => 'sum',
        'type' => 'price',
        'currency_code' => $store->getBaseCurrency()->getCode(),
        'filter_index' => 'sum',
        'filter_condition_callback' => array($this, '_filterSum'),
    ));

    return parent::_prepareColumns();
}

protected function _filterSum($collection, $column) {
    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }

    if (isset($value['from'])) {
        $collection->getSelect()->having('sum > '.$value['from']);
    }
    if (isset($value['to'])) {
        $collection->getSelect()->having('sum < '.$value['to']);
    }

    $this->setCollection($collection);
    return $this;
}

But this time I get this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sum' in 'having clause', query was: SELECT COUNT(*) FROM `customer_entity` AS `e`
 WHERE (`e`.`entity_type_id` = '1') GROUP BY `e`.`email` HAVING (sum > 0)

So, my question is how can I display the sum of two attributes in an admin grid?

Best Answer

Thanks for this wonderful question.

You can use the grid renderer feature to do this job. So specify your custom renderer in your sum grid column like below:

$this->addColumn('sum', array(
    'header' => Mage::helper('customer')->__('SUM'),
    'index' => 'sum',
    'type' => 'text',
    'renderer' => 'Namespace_Module_Block_Adminhtml_Customer_Grid_Renderer_Sum',
));

Now you need to define this render class inside your module Namespace_Module.

File: app/code/[local]/[Namespace]/[Module]/Block/Adminhtml/Customer/Grid/Renderer/Sum.php

<?php
class Namespace_Module_Block_Adminhtml_Customer_Grid_Renderer_Sum extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{

    /**
     * Render sum total of attr1 and attr2.
     *
     * @param Varien_Object $row
     * @return string
     */
    public function render(Varien_Object $row)
    {
        $sum   = null;
        $attr1 = $row->getData('attr1');
        $attr2 = $row->getData('attr2');
        if (isset($attr1) && isset($attr2)) {
            $sum = $attr1 + $attr2;
        }

        return Mage::helper('core')->currency($sum, true, false);;

    }
}

I hope the answer will give you some insights on how to approach this situation. Let me know if you need any clarifications.

Related Topic