3

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?

  • In my case addExpressionFieldToSelect did the job (as I use non EAV attributes but mere fields of a custom table). – nicolallias Mar 22 at 10:14
0

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.

  • Thanks. I tested your suggestion. The same error occurs: General error: 1111 Invalid use of group function... – Pedram Behroozi Jan 9 '18 at 8:21
  • You don't need to add addExpressionFieldToSelect() in your collection. please remove that if you are still using it. – Rajeev K Tomy Jan 9 '18 at 9:48
  • So what should I set for index key? Should I still use sum? – Pedram Behroozi Jan 9 '18 at 10:41
  • keep it as sum – Rajeev K Tomy Jan 10 '18 at 12:03
  • It doesn't work: Invalid attribute sum. Because if I remove addExpressionAttributeToSelect part there will be no sum field in the query. – Pedram Behroozi Jan 10 '18 at 12:38
0

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.

  • Since there were no better answers, I mark this as the correct answer. – Pedram Behroozi Jan 13 '18 at 7:30
0

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..!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.