5

I'm trying to add an expression column to collection in customer grid. But it not sortable. I'm not sure that using addExpressionAttributeToSelect or addExpressionFieldToSelect function can solve this problem. I don't know how to use them in my case.

As now,my code looks like as follows: (or view my old question here)

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

My joined function:

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')
      );
  }

Added Column:

$this->addColumn('order_count', array(
      'header' => Mage::helper('customer')->__('Total Orders'),
      'index' => 'order_count',
      'filter_index' => 'pages_with_number_of_views.order_count',
      'filter_condition_callback' => array($this, '_roleFilter'),
    ));

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

Sorry for the cross post, but I think we should get rid of the unanswered questions.

Thanks to kiatng this question is answered over at stackoverflow

After the join, you need to add the joined field to the array _map declared in Varien_Data_Collection_Db, for example:

$this->_map['fields']['stock_qty'] = 'stock.qty';

[edit] As pointed out by @sh4dydud3_88, you can do this:

$collection->addFilterToMap('stock_qty', 'stock.qty');

which will add the field stock_qty for filtering. Then you can filter with

$collection->addFieldToFilter('stock_qty', array('gt', 10));

Another example:

class Company_Mohe_Model_Resource_Im_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract
{
protected function _construct()
{
    $this->_init('mohe/im');
}  


public function joinIhe()
{
    $this->getSelect()->join(array('ihe' => $this->getTable('mohe/ihe')),
                                  'main_table.mic_inst_id = ihe.im_id',
                                  array('ihe_name'=>'name', 'ihe_ifms_id'=>'ifms_id')); 
    //$this->_map['fields'] = array('ihe_name'=>'ihe.name', 'ihe_ifms_id'=>'ihe.ifms_id'); //incorrect method
    $this->addFilterToMap('ihe_name', 'ihe.name'); //correct method, see comment by @sh4dydud3_88                           
    return $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.