0

I've just added 2 new columns to my grid that calculated total order & total price of them for customer grid:

Here is my _prepareCollection function:

protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel('customer/customer_collection')
            ->addNameToSelect()
            ->addAttributeToSelect('email')
            ->addAttributeToSelect('created_at')
            ->addAttributeToSelect('group_id');

        // zend_debug::dump($collection->getData());die;
        $total_pricesql ='SELECT sum(o.base_grand_total)'
            . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
            . ' WHERE o.customer_id = e.entity_id ';
        $expr = new Zend_Db_Expr('(' . $total_pricesql . ')'); 

        $collection->getSelect()->from(null, array('total_price'=>$expr));

        $totalsql ='SELECT COUNT(*)'
            . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
            . ' WHERE o.customer_id = e.entity_id ';
        $totalsqlexpr = new Zend_Db_Expr('(' . $totalsql . ')'); 

        $collection->getSelect()->from(null, array('orders_count'=>$totalsqlexpr));
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

Here is my code to add 2 column to grid:

$this->addColumn('orders_count', array(
            'header'    => Mage::helper('customer')->__('Total Orders'),
            'index'     => 'orders_count',
            // 'filter_condition_callback' => array($this, '_roleFilter'),
        ));

        $this->addColumn('total_price', array(
            'header' => Mage::helper('sales')->__('Total Sales'),
            'index' => 'total_price',
            'type'  => 'price',
            'currency_code' => Mage::app()->getStore()->getBaseCurrency()->getCode(),
        ));

But when I tried to filter them, I got the following error:

Fatal error: Call to a member function getBackend() on a non-object in /home/devmb/public_html/sites/dipacommerce/app/code/core/Mage/Eav/Model/Entity/A‌​bstract.php on line 816 

Can anyone help me please ?

2

1 Answer 1

1
  • First, you should definitely do these codes in resource models, not in blocks.
  • Second, you should use a joinLeft, not from.
  • Third, you need GROUP BY on your queries, since you're using SUM and COUNT aggregates..

Anyway, here's what you should do:

  1. Modify Mage_Customer_Model_Entity_Customer_Collection and add this method:

    public function addSalesInfoPerCustomer() {
    $this->getSelect()
        ->joinLeft(
            array('s' => new Zend_Db_Expr('(SELECT sum(o.base_grand_total) total_sales, COUNT(*) as order_count, customer_id'
                . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/order') . ' AS o'
                . ' GROUP BY customer_id)')),
            "s.customer_id = e.entity_id",
            array('total_sales', 'order_count')
        );
    }
    
  2. On your _prepareCollection method, call $collection->addSalesInfoPerCustomer();

  3. Call addColumn and take note of the column names total_sales and order_count (You need to modify your code to match these)

4
  • really nice suggested, but I've just tried and get that error:Parse error: syntax error, unexpected '[', expecting ')' in /home/devmb/public_html/sites/dipacommerce/app/code/core/Mage/Customer/Model/Resource/Customer.php on line 67
    – HungDQ
    Jun 23, 2015 at 4:22
  • I'm using magento 1.6 so I copy addSalesInfoPerCustomer() function to Mage_Customer_Model_Resource_Customer.
    – HungDQ
    Jun 23, 2015 at 4:23
  • Try again, I removed the [ and used array( instead. It has something to do with PHP version, sorry. Jun 23, 2015 at 5:28
  • 1
    I've tried again but this error still happen: Fatal error: Call to a member function getBackend() on a non-object in /home/devmb/public_html/sites/dipacommerce/app/code/core/Mage/Eav/Model/Entity/Abstract.php on line 816
    – HungDQ
    Jun 23, 2015 at 6:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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