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: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
I hope the answer will give you some insights on how to approach this situation. Let me know if you need any clarifications.