Magento - Integrity constraint violation: 1052 - Shipping Method

Question

The following code allows to add a column to orders/sales page in which displays the shipping method as shipping description containing all delivery carriers.

_prepareCollection

$collection->getSelect()->join('sales_flat_order', 'main_table.entity_id = sales_flat_order.entity_id',array('shipping_description'));

_prepareColumn

$this->addColumn('shipping_method', array(
'header' => Mage::helper('sales')->__('Shipping Method'),
'index' => 'shipping_description',
'filter_index' => 'main_table.status',
));

The shipping method appears perfect after I compile everything. Now when I try to sort the orders by prcessing and hit on Search it comes to this error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'status' in where clause is ambiguous, query was: SELECT COUNT(*) FROM `sales_flat_order_grid` AS `main_table` INNER JOIN `sales_flat_order` ON main_table.entity_id = sales_flat_order.entity_id WHERE (status = 'processing')

After some research it means that there is a double status when tables are jointed, now how to I create an alians from this and how to I apply it. Also if anybody knows the way to filter/sort by this column would be awesome.

PS. The file is located at: app/code/local/Mage/Adminhtml/Block/Sales/Order

EDIT: Now am trying to figure out how to create options dropdown based on the values in the database. Here is the code I am trying to get to work:

private static $_deliveryGroups = array();

public static function getDeliveryArray() {
if (count(self::$_deliveryGroups) == 0) {
    $delivery_group = new Mage_Shipping_Model_Group();
    $delivery_groups = $delivery_group->getColection()->toOptionHash();
    self::$_deliveryGroups = $delivery_groups;
}
return self::$_deliveryGroups;
}

And additional values in the Grid.php

    $this->addColumn('shipping_method', array(
    'header' => Mage::helper('sales')->__('Shipping Method'),
    'renderer'  => 'Vehence_Module_Block_Adminhtml_Block_Sales_Order_Desc',
    'type' => 'options',
    'options' => Vehence_Module_Block_Adminhtml_Block_Sales_Order_Desc::getDeliveryArray(),
    ));

And this:

protected function _getAttributeOptions($attribute_code)
    {
        $attribute = Mage::getModel('sales/order')->getAttribute('shipping_description', $attribute_code);
        $options = array();
        foreach( $attribute->getSource()->getAllOptions(true, true) as $option ) {
            $options[$option['value']] = $option['label'];
        }
        return $options;
}

Answer 1

You need to use custom renderer for your new field. Then in renderer method you have orderId. With that ID you can instantiate order object and get and output any field you like.

Here is an example: https://magento.stackexchange.com/questions/62856/how-to-get-latest-order-comment-on-the-order-grid-column/62859#62859

class Vendor_Module_Block_Renderer_Shipping extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{
    public function render(Varien_Object $row)
    {
        $orderId = $row->getId();
        $order = Mage::getModel('sales/order')->load($orderId);
        return $order->getData('shipping_description');
    }
}

To the following method (better with rewrite) add this:

$this->addColumn('history', array(
    'header' => Mage::helper('sales')->__('History status'),
    'type'  => 'text',
    'renderer' => new Vendor_Module_Block_Renderer_Shipping()
));

upd.1

So... I didn't applied any filters to my solution. The problem was in order collection. Visually it looks fine, but all filters are applying collection which doesn't contain necessary field for filtration. That because I refused my solution.

That because I thought that your solution was much better for that purpose and I decided to fix it. I created git repository for that module. It should works. Check this: https://github.com/zhartaunik/AddField

Hope it will be helpful