Add two attribute values and display the result as a column in admin grid

Question

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?

Answer 1

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.

Answer 2

The reason why I got the Column not found: 1054 Unknown column 'sum' in 'having clause' is Magento removes everything after SELECT statement in order to replace them with COUNT(*). You can see it in lib/Varien/Data/Collection/Db.php:

/**
 * Get SQL for get record count
 *
 * @return Varien_Db_Select
 */
public function getSelectCountSql()
{
    $this->_renderFilters();

    $countSelect = clone $this->getSelect();
    $countSelect->reset(Zend_Db_Select::ORDER);
    $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
    $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
    $countSelect->reset(Zend_Db_Select::COLUMNS); // <--

    $countSelect->columns('COUNT(*)');

    return $countSelect;
}

So the result query, as mentioned in the error message was this:

SELECT COUNT(*) FROM `customer_entity` AS `e`
 WHERE (`e`.`entity_type_id` = '1') GROUP BY `e`.`email` HAVING (sum > 0)

Hence the error.

But it doesn't remove attributes added using addAttributeToFilter method. So I "kind of" solved my problem by filtering attr1 and attr2 using a filter I know is always true:

protected function _prepareCollection() {
    $collection = Mage::getResourceModel('customer/customer_collection')
        ->addAttributeToSelect('email')
        ->addAttributeToSelect('attr1', 'left')
        ->addAttributeToSelect('attr2', 'left');
        ->addExpressionAttributeToSelect('sum', 'SUM({{attr1}})+SUM({{attr2}})', array(
            'attr1', 'attr2'
        )
        // I added these dummy filters:
        ->addAttributeToFilter('attr1', array(
            array('gt' => -1000000000000),
            array('null' => true)
        ))
        ->addAttributeToFilter('attr2', array(
            array('gt' => -1000000000000),
            array('null' => true)
        );

    $collection->getSelect()->group('e.email');
    $this->setCollection($collection);
    parent::_prepareCollection();

    return $this;
}

I would be very happy if anyone could come up with a better solution.

Answer 3

This is an easiest way to solve your problem.

First you need to get concat two attributes as one attribute by using CONCAT function

In Prepare Collection,

$collection->join(array('soas' => 'sales/order_address'), 'soas.parent_id=main_table.entity_id', 
                    array('full_billing_address'=>'CONCAT( soas.street, ",", soas.city, ",", soas.region)'
                  ), null,'left');

In Prepare Columns function,

$this->addColumn('full_billing_address', array(
            'header' => Mage::helper('sales')->__('Bill Address'),
            'index' => 'full_billing_address',
            'filter_index' => 'CONCAT( soas.street, ",", soas.city, ",", soas.region)',
        ));

(or)

$this->addColumn('full_billing_address', array(
            'header' => Mage::helper('sales')->__('Bill Address'),
            'index' => 'full_billing_address',
            'filter_condition_callback' => array($this, '_billAddressFilter'),
        ));

And put the following function in grid file if You used ''filter_condition_callback' => array($this, '_billAddressFilter'),'

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

        $this->getCollection()->getSelect()->where("CONCAT(soas.street, ',', soas.city, ',', soas.region) LIKE '%".$value."%'");

        return $this;
    }

Try this..!