Order Grid – Integrity Constraint Violation: 1052 Column ‘base_grand_total’ in Where Clause is Ambiguous

order-grid

I've been able to add a few custom attributes to the sales order grid (sku, custom name). I am able to filter correctly. When I add the customer email, I can add and filter it too, but then if I filter by a total (e.g. base grand total) I get the error above. Other solutions suggest using filter_index to remove this ambiguity but this hasn't helped, presumably because it's on a field I'm not adding (grand total) – so this is not due to ambiguous entity_id. How do I make it use the specific total field?

Here are the helpful parts:

Observer

 class Kaska_ExtendedGrid_Model_Observer
{

public function salesOrderGridCollectionLoadBefore($observer)
{

    $collection = $observer->getOrderGridCollection();
    $select = $collection->getSelect();

    $select->join('m_sales_flat_order', '`m_sales_flat_order`.entity_id = `main_table`.entity_id', array('email'  => new Zend_Db_Expr('`m_sales_flat_order`.customer_email')));

    $select->joinLeft(array('payment' => $collection->getTable('sales/order_payment')), 'payment.parent_id=main_table.entity_id', array('payment_method' => 'method'));

    $select->join('m_sales_flat_order_item','`m_sales_flat_order_item`.order_id=`main_table`.entity_id', array('sku' => new Zend_Db_Expr('`m_sales_flat_order_item`.sku'),'name'  => new Zend_Db_Expr('`m_sales_flat_order_item`.name') ));

    $select->join('m_catalog_product_entity_varchar', '`m_catalog_product_entity_varchar`.attribute_id=163 AND `m_catalog_product_entity_varchar`.entity_id = `m_sales_flat_order_item`.`product_id`', array('attr'  => new Zend_Db_Expr('`m_catalog_product_entity_varchar`.value')));

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


}


public function filterEmail($collection, $column)
{

    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }
    $collection->getSelect()->having(
        "`m_sales_flat_order`.customer_email like ?", "%$value%");
    return $this;
}


.... filterSku etc

layout.xml

<sales_order_grid_update_handle>
    <reference name="sales_order.grid">
        <action method="addColumnAfter">
            <columnId>payment_method</columnId>
            <arguments>
                <header>Payment Method</header>
                <index>payment_method</index>
                <filter_index>payment.method</filter_index>
                <type>text</type>
            </arguments>
            <after>shipping_name</after>
        </action>
        <action method="addColumnAfter">
            <columnId>email</columnId>
            <arguments helper="kaska_extendedgrid/getEmailColumnParams" />
            <after>payment_method</after>
        </action>

Helper file

public function getEmailColumnParams(){
    return array(
    'header'    => 'Customer Email',
    'index'     => 'email',
    'type'        => 'text',
    'filter_index'=>'m_sales_flat_order.customer_email',
}

I also tried locally overriding the _prepareColumns of Mage/Adminhtml/Block/Sales/Order/Grid.php

$this->addColumn('base_grand_total', array(
        'header' => Mage::helper('sales')->__('G.T. (Base)'),
        'index' => 'base_grand_total',
        'type'  => 'currency',
        'currency' => 'base_currency_code',
     'filter_index' => 'sales_flat_order.base_grand_total'
    ));

EDIT
After some help from below, the culprit was another extension's overriding of the order grid preparecolumns

Best Answer

Try with this by using Zend_Db_Expr, not sure it will fix.

$this->addColumn('base_grand_total', array(
        'header' => Mage::helper('sales')->__('G.T. (Base)'),
        'index' => 'base_grand_total',
        'type'  => 'currency',
        'currency' => 'base_currency_code',
     'filter_index' => new Zend_Db_Expr("main_table.base_grand_total")
    ));

Related Solutions

Database Magento Order Grid – Add Column to a Grid Observer and Resolve Ambiguous Store ID Issue

Let's try this again with on other solution I mentioned before to you :-) , I have build the complete extension to show you how to add the field to the grid table. After that you only need a layout update file to add the column to you order grid page.

I called the extension Example_SalesGrid, but you can change it to your own needs.

Let's start by creating the module init xml in /app/etc/modules/Example_SalesGrid.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!--
 Module bootstrap file
-->
<config>
    <modules>
        <Example_SalesGrid>
            <active>true</active>
            <codePool>community</codePool>
            <depends>
                <Mage_Sales />
            </depends>
        </Example_SalesGrid>
    </modules>
</config>

Next we create our module config xml in /app/code/community/Example/SalesGrid/etc/config.xml:

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <modules>
        <Example_SalesGrid>
            <version>0.1.0</version> <!-- define version for sql upgrade -->
        </Example_SalesGrid>
    </modules>
    <global>
        <models>
            <example_salesgrid>
                <class>Example_SalesGrid_Model</class>
            </example_salesgrid>
        </models>
        <blocks>
            <example_salesgrid>
                <class>Example_SalesGrid_Block</class>
            </example_salesgrid>
        </blocks>
        <events>
            <!-- Add observer configuration -->
            <sales_order_resource_init_virtual_grid_columns>
                <observers>
                    <example_salesgrid>
                        <model>example_salesgrid/observer</model>
                        <method>addColumnToResource</method>
                    </example_salesgrid>
                </observers>
            </sales_order_resource_init_virtual_grid_columns>
        </events>
        <resources>
            <!-- initialize sql upgrade setup -->
            <example_salesgrid_setup>
                <setup>
                    <module>Example_SalesGrid</module>
                    <class>Mage_Sales_Model_Mysql4_Setup</class>
                </setup>
            </example_salesgrid_setup>
        </resources>
    </global>
    <adminhtml>
        <layout>
            <!-- layout upgrade configuration -->
            <updates>
                <example_salesgrid>
                    <file>example/salesgrid.xml</file>
                </example_salesgrid>
            </updates>
        </layout>
    </adminhtml>
</config>

Now we create the sql upgrade script in /app/code/community/Example/SalesGrid/sql/example_salesgrid_setup/install-0.1.0.php:

<?php
/**
 * Setup scripts, add new column and fulfills
 * its values to existing rows
 *
 */
$this->startSetup();
// Add column to grid table

$this->getConnection()->addColumn(
    $this->getTable('sales/order_grid'),
    'customer_group_id',
    'smallint(6) DEFAULT NULL'
);

// Add key to table for this field,
// it will improve the speed of searching & sorting by the field
$this->getConnection()->addKey(
    $this->getTable('sales/order_grid'),
    'customer_group_id',
    'customer_group_id'
);

// Now you need to fullfill existing rows with data from address table

$select = $this->getConnection()->select();
$select->join(
    array('order'=>$this->getTable('sales/order')),
    $this->getConnection()->quoteInto(
        'order.entity_id = order_grid.entity_id'
    ),
    array('customer_group_id' => 'customer_group_id')
);
$this->getConnection()->query(
    $select->crossUpdateFromSelect(
        array('order_grid' => $this->getTable('sales/order_grid'))
    )
);

$this->endSetup();

Next we create the layout update file in /app/design/adminhtml/default/default/layout/example/salesgrid.xml:

<?xml version="1.0"?>
<layout>
    <!-- main layout definition that adds the column -->
    <add_order_grid_column_handle>
        <reference name="sales_order.grid">
            <action method="addColumnAfter">
                <columnId>customer_group_id</columnId>
                <arguments module="sales" translate="header">
                    <header>Customer Group</header>
                    <index>customer_group_id</index>
                    <type>options</type>
                    <filter>Example_SalesGrid_Block_Widget_Grid_Column_Customer_Group</filter>
                    <renderer>Example_SalesGrid_Block_Widget_Grid_Column_Renderer_Customer_Group</renderer>
                    <width>200</width>
                </arguments>
                <after>grand_total</after>
            </action>
        </reference>
    </add_order_grid_column_handle>
    <!-- order grid action -->
    <adminhtml_sales_order_grid>
        <!-- apply the layout handle defined above -->
        <update handle="add_order_grid_column_handle" />
    </adminhtml_sales_order_grid>
    <!-- order grid view action -->
    <adminhtml_sales_order_index>
        <!-- apply the layout handle defined above -->
        <update handle="add_order_grid_column_handle" />
    </adminhtml_sales_order_index>
</layout>

Now we need two Block files, one to create the filter options, /app/code/community/Example/SalesGrid/Block/Widget/Grid/Column/Customer/Group.php:

<?php

class Example_SalesGrid_Block_Widget_Grid_Column_Customer_Group extends Mage_Adminhtml_Block_Widget_Grid_Column_Filter_Select  {

    protected $_options = false;

    protected function _getOptions(){

        if(!$this->_options) {
            $methods = array();
            $methods[] = array(
                'value' =>  '',
                'label' =>  ''
            );
            $methods[] = array(
                'value' =>  '0',
                'label' =>  'Guest'
            );

            $groups = Mage::getResourceModel('customer/group_collection')
                ->addFieldToFilter('customer_group_id', array('gt' => 0))
                ->load()
                ->toOptionArray();

            $this->_options = array_merge($methods,$groups);
        }
        return $this->_options;
    }
}

And the second to translate the row values to the correct text that will be displayed, /app/code/community/Example/SalesGrid/Block/Widget/Grid/Column/Renderer/Customer/Group.php:

<?php

class Example_SalesGrid_Block_Widget_Grid_Column_Renderer_Customer_Group extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract   {

    protected $_options = false;

    protected function _getOptions(){

        if(!$this->_options) {
            $methods = array();
            $methods[0] = 'Guest';

            $groups = Mage::getResourceModel('customer/group_collection')
                ->addFieldToFilter('customer_group_id', array('gt' => 0))
                ->load()
                ->toOptionHash();
            $this->_options = array_merge($methods,$groups);
        }
        return $this->_options;
    }

    public function render(Varien_Object $row){
        $value = $this->_getValue($row);
        $options = $this->_getOptions();
        return isset($options[$value]) ? $options[$value] : $value;
    }
}

The last file needed is only needed if you create an extra column from a table other than sales/order (sales_flat_order). All fields in sales/order_grid matching the column name from sales/order is automatically updated in the sales/order_grid table. If you need to add the payment option for example you will need this observer to add the field to the query so the data can be copied to the correct table. The observer used for this is in /app/code/community/Example/SalesGrid/Model/Observer.php:

<?php
/**
 * Event observer model
 *
 *
 */
class Example_SalesGrid_Model_Observer {

    public function addColumnToResource(Varien_Event_Observer $observer) {
        // Only needed if you use a table other than sales/order (sales_flat_order)

        //$resource = $observer->getEvent()->getResource();
        //$resource->addVirtualGridColumn(
        //  'payment_method',
        //  'sales/order_payment',
        //  array('entity_id' => 'parent_id'),
        //  'method'
        //);
    }
}

This code is based on the example from ~~http://www.ecomdev.org/2010/07/27/adding-order-attribute-to-orders-grid-in-magento-1-4-1.html~~

Hope the example above solves your problem.

SQLSTATE[42S22] Error – How to Solve Unknown Column ’email’ in ‘where clause’

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>

Best Answer

Related Solutions

Database Magento Order Grid – Add Column to a Grid Observer and Resolve Ambiguous Store ID Issue

SQLSTATE[42S22] Error – How to Solve Unknown Column ’email’ in ‘where clause’

Related Topic