Sales order grid Filter & Sorting not working in M

问题:

I would like to show the customer email in Magento sales order grid. I have to rewrite the Mage_Adminhtml_Block_Sales_Order_Grid in my local module to added the new column for customer email. I got the value of email for each order but sorting and filtering not working as expected.

I was spent more than a day for sorting this issue but no luck. Also, I referred few of answers in SO too.

Below is the code which is I have tried referring this answer,

   public function setCollection($collection)
{
    $collection->getSelect()->joinLeft(
        array('sfo'=>'sales_flat_order'),
        'main_table.entity_id=' . 'sfo' . '.entity_id',
        array('*')
    );

    $collection->getSelect()->joinLeft(
        array('sfoa'=>'sales_flat_order_address'),
        'main_table.entity_id=' . 'sfoa' . '.parent_id',
        array('email')
    );

    $collection->getSelect()->group(array('main_table.entity_id'));

    $collection->addFilterToMap('increment_id', 'main_table.increment_id');

    parent::setCollection($collection);
}

protected function _prepareColumns()
{
    $this->addColumnAfter('email', array(
        'header' => Mage::helper('sales')->__('Customer Email'),
        'width' => '80px',
        'type' => 'text',
        'index' => 'email',
        'filter_index' => 'sfoa.email',
        'filter_condition_callback' => 'filter_last_login',
        'order_callback'            => 'sort_last_login',
    ), 'erp_confirm_order');

    return parent::_prepareColumns();
}



public function getGridUrl()
{
    return $this->getUrl('*/*/grid', array('_current'=>true));
}

function filter_last_login($collection, $column)
{
    if (!$column->getFilter()->getCondition()) {
        return;
    }

    $condition = $collection->getConnection()
        ->prepareSqlCondition('sfoa.email', $column->getFilter()->getCondition());
    $collection->getSelect()->where($condition);
}

function sort_last_login($collection, $column)
{
    $collection->getSelect()->order($column->getIndex() . ' ' . strtoupper($column->getDir()));
}

protected function _setCollectionOrder($column)
{
    if ($column->getOrderCallback()) {
        call_user_func($column->getOrderCallback(), $this->getCollection(), $column);

        return $this;
    }

    return parent::_setCollectionOrder($column);
}

Edit 1

Nothing works when I sorting & filtering the customer email column, Also i am getting the error when I click rest of default grid columns.

Integrity constraint violation: 1052 Column 'increment_id' in order clause is ambiguous, query was: SELECT `main_table`.*, `sfo`.*, `sfoa`.`email` FROM `sales_flat_order_grid` AS `main_table`
 LEFT JOIN `sales_flat_order` AS `sfo` ON main_table.entity_id=sfo.entity_id
 LEFT JOIN `sales_flat_order_address` AS `sfoa` ON main_table.entity_id=sfoa.parent_id GROUP BY `main_table`.`entity_id` ORDER BY increment_id ASC LIMIT 20

Any help much appreciation. Thanks,

回答1:

So, the problem you're facing that differs from the original answer is that you are joining on fields that have ambiguous names. You need to specify which fields to filter against. I've faced this before myself.

When you add columns from the sales_flat_order you shouldn't need to do the custom filter or sort callback. That only is necessary when you need some more complicated filtering/sorting query like the original example with last login date. Just make sure your 'filter_index' is set and it will work fine. The below example is working for adding customer email from the billing address.

class My_Namespace_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
{
    public function setCollection($collection)
    {
        $collection->join(['sfoa' => 'sales/order_address'], 'main_table.entity_id=sfoa.parent_id AND sfoa.address_type="billing"', ['billing_email' => 'sfoa.email']);

        parent::setCollection($collection);
    }

    public function _prepareColumns()
    {
        parent::_prepareColumns();

        foreach ($this->getColumns() as $column) {
            if (!$column->getFilterIndex()) {
                $column->setFilterIndex('main_table.'.$column->getIndex());
            }
        }

        $this->addColumnAfter('customer_email', [
            'header' => Mage::helper('customer')->__('Customer Email'),
            'width'  => '50px',
            'index'  => 'billing_email',
            'filter_index' => 'sfoa.email',
        ], 'shipping_name');

        $this->sortColumnsByOrder();
        return $this;
    }
}

Here is an example from the order table itself:

class My_Namespace_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
{
    public function setCollection($collection)
    {
        $collection->join(['sfo' => 'sales/order'], 'main_table.entity_id=sfo.entity_id', 'customer_email');

        parent::setCollection($collection);
    }

    public function _prepareColumns()
    {
        parent::_prepareColumns();

        foreach ($this->getColumns() as $column) {
            if (!$column->getFilterIndex()) {
                $column->setFilterIndex('main_table.'.$column->getIndex());
            }
        }

        $this->addColumnAfter('customer_email', [
            'header' => Mage::helper('customer')->__('Customer Email'),
            'width'  => '50px',
            'index'  => 'customer_email',
            'filter_index' => 'sfo.customer_email',
        ], 'shipping_name');

        $this->sortColumnsByOrder();
        return $this;
    }
}