在“销售订单网格"字段中使用列别名 [英] Using column aliases in Sales Order Grid field

问题描述

我正在尝试向Magento 1.7CE的后端销售网格添加两个字段(Shipping Postcode和Billing Postcod).

I'm trying to add two fields (Shipping Postcode and Billing Postcod) to Magento 1.7CE's backend Sales grid.

我这样做是通过覆盖Mage_Adminhtml_Block_Sales_Order_Grid::setCollection(...)将表与sales/order_address联接.

I'm doing this by overriding Mage_Adminhtml_Block_Sales_Order_Grid::setCollection(...) to join the table with sales/order_address.

public function setCollection($collection){
    parent::setCollection($collection);
    $collection->getSelect()->join(
        array('address_shipping' => $collection->getTable("sales/order_address")),
        'main_table.entity_id = address_shipping.parent_id AND address_shipping.address_type = "shipping"',
        array('postcode')
    );
    $collection->getSelect()->join(
        array('address_billing' => $collection->getTable("sales/order_address")),
        'main_table.entity_id = address_billing.parent_id AND address_billing.address_type = "billing"',
        array('postcode')
    );
}

和Mage_Adminhtml_Block_Sales_Order_Grid::_prepareColumns(...)将列添加到Sales Grid.

And Mage_Adminhtml_Block_Sales_Order_Grid::_prepareColumns(...) to add the columns to Sales Grid.

protected function _prepareColumns(){
    $this->addColumn('shipping_postcode', array(
        'header' => Mage::helper('sales')->__('Shipping Postcode'),
        'index' => 'postcode',
    ));
    $this->addColumn('billing_postcode', array(
        'header' => Mage::helper('sales')->__('Billing Postcode'),
        'index' => 'postcode',
    ));
    return parent::_prepareColumns();
}

问题是两次都需要从sales/order_address表中选择postcode字段，这会导致SQL错误

The issue is that both times I need to select postcode field from sales/order_address table which results in SQL error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'postcode' in order clause is ambiguous

我试图通过修改setCollection(...)传递array('shipping_postcode'=>'postcode')和array('billing_postcode'=>'postcode')以及将_prepareColumns(...)改为'index' => 'shipping_postcode'和'index' => 'billing_postcode'来使用MySQL的AS别名来区分两个邮政编码.这导致了另一个SQL错误

I have tried to use MySQL's AS alias to differentiate between two postcodes, by modifying setCollection(...) to pass array('shipping_postcode'=>'postcode') and array('billing_postcode'=>'postcode') as well as changing _prepareColumns(...) to say 'index' => 'shipping_postcode' and 'index' => 'billing_postcode'. This resulted in another SQL error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'shipping_postcode' in 'where clause'

如何在销售网格"中同时拥有这两个列?

How can I achieve having both columns in the Sales Grid?

推荐答案

让我们尝试第一个代码

public function setCollection($collection){
    parent::setCollection($collection);
    $collection->getSelect()->join(
        array('address_shipping' => $collection->getTable("sales/order_address")),
        'main_table.entity_id = address_shipping.parent_id AND address_shipping.address_type = "shipping"',
        array('address_shipping.postcode as shippingpostcode')
    );

    $collection->getSelect()->join(
        array('address_billing' => $collection->getTable("sales/order_address")),
        'main_table.entity_id = address_billing.parent_id AND address_billing.address_type = "billing"',
        array('address_billing.postcode as billingpostcode')
    );
}

第二个_prepareColumns()在这里，

protected function _prepareColumns(){
     $this->addColumn('shippingpostcode', array(
        'header' => Mage::helper('sales')->__('Shipping Postcode'),
        'index' => 'shippingpostcode',
        'filter_index' => 'address_shipping.postcode'
     ));

    $this->addColumn('billingpostcode', array(
        'header' => Mage::helper('sales')->__('Billing Postcode'),
        'index' => 'billingpostcode',
        'filter_index' => 'address_billing.postcode'  
    ));

return parent::_prepareColumns();

}

如果您想了解有关'filter_index'的更多信息，请在其中注释/注释，然后尝试在网格中对邮政编码列进行排序.您会看到不同的结果.如果删除filter_index，则排序错误.

if you want to know more about 'filter_index', comment in/out in this, then try sorting in your grid for post code column. You will see different result. If you remove filter_index, error in sorting.

这篇关于在“销售订单网格"字段中使用列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！