1

I am getting the above error on the Customer/Orders tab in Magento admin which is related to a module that is adding columns to the sales_order_grid. I chosen the Observer method since there is another module extending the grid.

I have used an Observer and a layout.xml file in order to add the columns to the Grid but for some reasone it is having an adverse affect on the Customer/Orders tab.

Here is my Observer

public function salesOrderGridCollectionLoadBefore($observer)
{
    $collection = $observer->getOrderGridCollection();

    $select = $collection->getSelect();


    if(Mage::getStoreConfig('sales/ordergrid/postcode'))
    {
        $select->joinLeft(array(
                'sfoa'=>'sales_flat_order_address'),
            'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="billing"',
            array(
                'sfoa.postcode',
                'sfoa.email'
            )
        );
    }

    if(Mage::getStoreConfig('sales/ordergrid/skus')||(Mage::getStoreConfig('sales/ordergrid/items')))
    {

        $select->join(array(
                    'item'=>$collection->getTable('sales/order_item')),
                'item.order_id=`main_table`.entity_id AND item.product_type="simple"',
                array(
                    'skus' => new Zend_Db_Expr('group_concat(item.sku SEPARATOR ", ")'),
                    'name' => new Zend_Db_Expr('group_concat(item.name SEPARATOR ", ")')
                     )
                 );
    }

    if(Mage::getStoreConfig('sales/ordergrid/payment'))
    {
        $select->joinLeft(array(
                    'payment' => $collection->getTable('sales/order_payment')),
                'payment.parent_id=main_table.entity_id', 
                array(
                    'payment_method' => 'method'
                     )
                );
    }

    if(Mage::getStoreConfig('sales/ordergrid/coupon'))
    {
        $select->joinLeft('sales_flat_order',
                'main_table.entity_id = sales_flat_order.entity_id',
                array(
                    'coupon_code'
                     )
                );
    }

    $select->group('main_table.entity_id');

    if ($where = $select->getPart('where')) {
        foreach ($where as $key=> $condition) {
            if (strpos($condition, 'created_at')) {
                $new_condition = str_replace("created_at", "main_table.created_at", $condition);
                $where[$key] = $new_condition;
            }
                            if (strpos($condition, 'status')) {
                                    $new_condition = str_replace("status", "main_table.status", $condition);
                                    $where[$key] = $new_condition;
                            }
        }
        $select->setPart('where', $where);
    }

}

// This adds the mass action button

public function beforeBlockToHtml(Varien_Event_Observer $observer)
{
    $block = $observer->getEvent()->getBlock();
    if ($block instanceof Mage_Adminhtml_Block_Sales_Order_Grid) {
        $this->_modifySalesOrderGrid($block);
        $block->getMassactionBlock()->addItem('export', array(
                    'label' => 'Download CSV',
                    'url' => $block->getUrl('*/*/massExportAction'),
            ));
    }
}

protected function _removeColumn(Mage_Adminhtml_Block_Sales_Order_Grid $block, $columnName)
{
    $columns = $block->getColumns();
    unset($columns[$columnName]);        
    $this->_mutateProtectedProperty($block, '_columns', $columns);
}

protected function _mutateProtectedProperty($object, $propertyName, $value)
{
    $reflection = new ReflectionClass($object);
    $property = $reflection->getProperty($propertyName);
    $property->setAccessible(true);
    $property->setValue($object, $value);        
}

protected function _callProtectedMethod($object, $methodName)
{
    $reflection = new ReflectionClass($object);
    $method = $reflection->getMethod($methodName);
    $method->setAccessible(true);
    return $method->invoke($object);
}

protected function _modifySalesOrderGrid(Mage_Adminhtml_Block_Sales_Order_Grid $grid)
{

    $this->_removeColumn($grid, 'shipping_name');
    $this->_removeColumn($grid, 'base_grand_total');
    $this->_removeColumn($grid, 'grand_total');

    $grid->sortColumnsByOrder();
    $this->_callProtectedMethod($grid, '_prepareCollection');                

} 

My Observer uses an alias in order to make the columns filterable/searchable on the grid

Here is my layout.xml

 <action method="addColumnAfter" ifconfig="sales/ordergrid/postcode">
                <columnId>postcode</columnId>
                <arguments>
                    <header>Postcode</header>
                    <index>postcode</index>
                    <type>text</type>
                    <filter_index>sfoa.postcode</filter_index>
                    <width>70px</width>
                </arguments>
                <after>email</after>
            </action>

The module works fine on the Sales/Order_Grid but if I navigate to the Customer account page and click the Orders tab I get this error - I'm not sure how to solve it?

SELECT `main_table`.`entity_id`, `main_table`.`increment_id`, `main_table`.`customer_id`, `main_table`.`created_at`, `main_table`.`grand_total`, `main_table`.`order_currency_code`, `main_table`.`store_id`, `main_table`.`billing_name`, `main_table`.`shipping_name`, `sfoa`.`postcode`, `sfoa`.`email`, group_concat(item.sku SEPARATOR ", ") AS `skus`, group_concat(item.name SEPARATOR ", ") AS `name`, `payment`.`method` AS `payment_method`, `sales_flat_order`.`coupon_code` FROM `sales_flat_order_grid` AS `main_table` LEFT JOIN `sales_flat_order_address` AS `sfoa` ON main_table.entity_id = sfoa.parent_id AND sfoa.address_type="billing" INNER JOIN `sales_flat_order_item` AS `item` ON item.order_id=`main_table`.entity_id AND item.product_type="simple" LEFT JOIN `sales_flat_order_payment` AS `payment` ON payment.parent_id=main_table.entity_id LEFT JOIN `sales_flat_order` ON main_table.entity_id = sales_flat_order.entity_id WHERE (main_table.customer_id = '450973') GROUP BY `main_table`.`entity_id`

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sfoa.postcode' in 'field list'
  • Try to print the result query and analyse why request is wrong. $select->__toString(). – oleksii.svarychevskyi Jan 30 '14 at 13:27
  • @oleksii.svarychevskyi I added the query but what I cannot work out is why the module is having an adverse affect on the customer page??? – user1704524 Jan 30 '14 at 16:00
  • Does this query run on Customer Account page? – oleksii.svarychevskyi Jan 30 '14 at 16:05
  • @oleksii.svarychevskyi yes the query runs on the customer account page on the order tab which is where I am getting the error... – user1704524 Jan 30 '14 at 17:00
  • Can you run this sql directly in mysql? And check what result you will get. – oleksii.svarychevskyi Jan 30 '14 at 17:27