1

I am trying to extend the sales order grind in Magento in order to show Product Name, SKU, And Billing email. I've had to use an Observer since there is a module currently rewriting the Grid. I have the columns I need but I am unable to search them without getting this error.

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

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

        // Fix for ambiguous 'created at' columns. With extended/rewrite approach we can use:
        //      $collection->addFilterToMap('created_at', 'main_table.created_at');
        // However, with event observer something more creative has to be used to fix the query:

        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;
                }
            }
            $select->setPart('where', $where);
        }

        //Mage::log($select->__toString());

    }

    // 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');                

    }
2

Solved it...

Needed to add this to the layout.xml in order to filter

    <action method="addColumnAfter" ifconfig="sales/ordergrid/email">
        <columnId>email</columnId>
        <arguments>
            <header>Email</header>
            <index>email</index>
            <type>text</type>
            **<filter_index>sfoa.email</filter_index>**
            <width>80px</width>
        </arguments>
        <after>created_at</after>
    </action>
  • Please mark this as the correct answer, then. – philwinkle Jan 21 '14 at 3:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.