Cannot filter new column calculated in custom customer grid

Question

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 ?

Answer 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)