2

I have added in order count and last order date to the customer grid which is working fine, however it is not sortable or filterable in the grid.

I have done some searching but can't seem to find a solution that makes any sense, can someone explain how I can achieve this?

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel('customer/customer_collection')
        ->addNameToSelect()
        ->addAttributeToSelect('email')
        ->addAttributeToSelect('created_at')
        ->addAttributeToSelect('group_id')
        ->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
        ->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
        ->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
        ->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
        ->joinAttribute('billing_country_id', 'customer_address/country_id', 'default_billing', null, 'left')
        ->joinAttribute('billing_company', 'customer_address/company', 'default_billing', null, 'left');

    $sql = 'SELECT COUNT(*) FROM sales_flat_order AS o WHERE o.customer_id=e.entity_id';

    $expr = new Zend_Db_Expr('(' . $sql . ')');

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

    $sql ='SELECT MAX(o.created_at) FROM sales_flat_order AS o WHERE o.customer_id = e.entity_id ';
    $expr = new Zend_Db_Expr('(' . $sql . ')');

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

    $this->setCollection($collection);

    return parent::_prepareCollection();
}

//Rest of code omitted for brevity

protected function _prepareColumns()
{        
     //code omitted for brevity
     $this->addColumn('orders_count', array(
          'header'    => Mage::helper('customer')->__('Total Orders'),
          'align'     => 'left',
          'width'     => '40px',
          'index'     => 'orders_count',
          'type'  => 'number',
     ));

     $this->addColumn('last_order_date', array(
          'header'    => Mage::helper('customer')->__('Last Order Date'),
          'type'      => 'datetime',
          'align'     => 'center',
          'index'     => 'last_order_date',
          'gmtoffset' => true,
     ));
    //additional code omitted
}
  • Would you mind adding some context to your second code example? I'm assuming it's a snippet from the grid class' _prepareColumns() method, but it would be nice to have some clarity there for others. – tjons Dec 13 '16 at 14:03
  • @tjons sorry about that! Updated :) – KiwisTasteGood Dec 13 '16 at 14:07
  • No problem! Thanks for doing that. I just posted an answer, please let me know if it helps... – tjons Dec 13 '16 at 14:10
0

Columns in Magento are filterable and sortable by default when they correspond to a column in the database, but because you are creating a column that doesn't directly correspond to a table in the database, you have to specify which data to filter and sort on.

To add the column into the filtering process, use the filter_condition_callback element in the column definition array. Specify it as a callable: array($this, 'functionName'), where functionName is replaced by a custom function you write to perform the filtering on the column. To specify the sort, specify the sort_index element, defining it as the column to sort on.

  • Thanks! If I add 'filter' => 'adminhtml/filter_type_class' I get a blank white screen, adding 'sortable' => true, does nothing when I click to order the column they are all still in the wrong order – KiwisTasteGood Dec 13 '16 at 14:13
  • Well, you have to replace adminhtml/filter_type_class with the proper filter class name. – tjons Dec 13 '16 at 14:15
  • For the sort, there are two sort types: ascending and descending. I think descending is the default, but you may be looking for ascending. Click the top again. – tjons Dec 13 '16 at 14:24
  • @KiwisTasteGood Let me know if that works... – tjons Dec 13 '16 at 14:24
  • Thanks, can you point me in the direction of how I can create / find the filter_type_class I need? As for sorting I have tried ascending and descending with no luck see screenshot: pasteboard.co/9kM9JNzXq.png – KiwisTasteGood Dec 13 '16 at 14:26
0

My solution

need override method - _setCollectionOrder

will like

protected function _setCollectionOrder($column)
{
    if (!$dir = $column->getDir()) {
        return $this;
    }

    if($column->getIndex()=='orders_count') {
        mage::log($column->getIndex());
        $collection = $this->getCollection();

        $collection->getSelect()
            ->order("orders_count " . strtoupper($column->getDir()));
        return $this;
    }

    if($column->getIndex()=='orders_total') {
        mage::log($column->getIndex());
        $collection = $this->getCollection();

        $collection->getSelect()
            ->order("orders_total " . strtoupper($column->getDir()));
        return $this;
    }


    return parent::_setCollectionOrder($column);
}

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.