4

I made in customers admin grid a join with sales_flat_order table:

protected function _preparePage()
{     
    $this->getCollection()->joinTable('sales_flat_order', 
                                        'customer_id = entity_id', 
                                        array('ordered' => new Zend_Db_Expr('SUM(subtotal_invoiced)'),), 
                                        null, 
                                        'left');   
    $this->getCollection()->getSelect()->group('entity_id'); 
    return parent::_preparePage();
} 

protected function _prepareColumns()
{     
    $this->addColumnAfter('ordered', array(
        'header'    => Mage::helper('customer')->__('Ordered'),
        'index'     => 'ordered',            
    ),'name');                   
    return parent::_prepareColumns();
}

It works fine, until I try to use sort by field ordered. Magento put into query

ORDER BY sales_flat_order.SUM(subtotal_invoiced) 

and this produces SQL error

FUNCTION sales_flat_order.SUM does not exist.

How can I remove sales_flat_order from ORDER BY so it would be just ORDER BY SUM(subtotal_invoiced)? In select it doesn't put that, it stays SUM(subtotal_invoiced) AS ordered, I don't know why it put in ORDER BY this.

2

You cannot use Zend_Db_Expr as third param of joinTable, because it should be the array which contains the list of field names from joined table. Try to get select from collection and to use join function for your purpose.

 $this->getCollection()->getSelect()->join(
     array('sfo'=>'sales_flat_order'), 
     'sfo.customer_id = main_table.entity_id', 
     array('ordered' => new Zend_Db_Expr('SUM(sfo.subtotal_invoiced)')) 
 ); 

Hope that will help you.

  • Tnx but this also doesn't work. I replaced main_table.entity_id with e.entity_id, otherwise it makes error. But in this way it doesn't put any ORDER BY condition, it just displays results without ordering, even if the field is in admin grid marked as to be used for ordering. I tried in both _prepareCollection and _preparePage. – JohnyFree Feb 3 '14 at 8:34
0

May be the problem is here.
when you apply group on collection magento collection counts get breaks
Overwrite this file
app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php

protected function _getSelectCountSql($select = null, $resetLeftJoins = true)
    {
       $this->_renderFilters();
       $countSelect = (is_null($select)) ?
           $this->_getClearSelect() :
           $this->_buildClearSelect($select);

       // group by fix
       if(count($countSelect->getPart(Zend_Db_Select::GROUP)) > 0) {
           $countSelect->reset(Zend_Db_Select::GROUP);
       }


       $countSelect->columns('COUNT(DISTINCT e.entity_id)');
       if ($resetLeftJoins) {
           $countSelect->resetJoinLeft();
       }
       return $countSelect;
    }

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.