销售订单网格过滤器无法在Magento中进行排序 [英] Sales order grid Filter & Sorting not working in Magento

问题描述

我想在Magento销售订单网格中显示客户电子邮件.我必须在本地模块中重写Mage_Adminhtml_Block_Sales_Order_Grid，以添加用于客户电子邮件的新列.我得到了每个订单的电子邮件的价值，但排序和过滤无法按预期工作.

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

编辑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,

推荐答案

因此，您面临的与原始答案不同的问题是，您要加入名称不明确的字段.您需要指定要过滤的字段.我已经面对了这个问题.

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.

当您从sales_flat_order添加列时，无需执行自定义过滤器或排序回调.仅当您需要一些更复杂的过滤/排序查询(例如带有上次登录日期的原始示例)时，才需要这样做.只需确保已设置"filter_index"即可正常运行.下面的示例用于从帐单地址添加客户电子邮件.

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

这篇关于销售订单网格过滤器无法在Magento中进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！