9

I am trying to create a customer grid module that accesses different table columns from sales_order information such as billing, payment method etc..

I have the grids show the correct data but when I search the order number field I get

"SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'increment_id'

Can anyone tell me how I can solve this so I can search any field from the grid in order to sort the data please?

Grid.php

<?php

class Custom_Module_Block_Adminhtml_Order_Grid extends Mage_Adminhtml_Block_Widget_Grid
{

    public function __construct()
    {
        parent::__construct();
        $this->setId('sales_order_grid');
        $this->setUseAjax(false);
        $this->setDefaultSort('created_at');
        $this->setDefaultDir('DESC');
        $this->setSaveParametersInSession(true);
    }

    protected function _getCollectionClass()
    {
        return 'sales/order_grid_collection';
    }

    protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel($this->_getCollectionClass());
        $collection->getSelect()->joinLeft('sales_flat_order_payment',
        'sales_flat_order_payment.parent_id = main_table.entity_id',array('cc_type',
        'cc_number_enc', 'cc_type', 'last_trans_id'));

        $collection->getSelect()->joinLeft('sales_payment_transaction',
            'sales_payment_transaction.transaction_id = main_table.entity_id',array('payment_id'));

        $collection->getSelect()->joinLeft(
                'customer_entity',
                'main_table.customer_id = customer_entity.entity_id', array('customer_name' => 'email'));

        $collection->getSelect()->joinLeft(
                'ops_alias',
                'sales_flat_order_payment.parent_id = main_table.entity_id', array('alias','brand', 'payment_method'));

        $this->setCollection($collection);
        return parent::_prepareCollection();
    }


    protected function _prepareColumns()
    {
        $this->addColumn('real_order_id', array(
            'header'=> Mage::helper('sales')->__('Order #'),
            'width' => '80px',
            'type'  => 'text',
            'index' => 'increment_id',

        ));

        $this->addColumn("created_at", array(
            "header" => Mage::helper("sales")->__("Order Date"),
            "index" => "created_at",
        ));

        $this->addColumn("billing_name", array(
            "header" => Mage::helper("sales")->__("Billing Name"),
            "index" => "billing_name",
        ));

        $this->addColumn("customer_name", array(
            "header" => Mage::helper("sales")->__("Customer Email"),
            "index" => "customer_name",
        ));

        $this->addColumn("payment_method", array(
            "header" => Mage::helper("sales")->__("Payment Method"),
            "index" => "payment_method",
        ));

        $this->addColumn('payment_id', array(
            'header'        => Mage::helper('sales')->__('Payment ID'),
            'align'         => 'right',
            'index'         => 'payment_id',
        ));

        $this->addColumn('brand', array(
            'header'    => Mage::helper('ops')->__('Credit Card Type'),
            'index'     => 'brand',
        ));

        $this->addColumn('alias', array(
            'header'        => Mage::helper('ops')->__('Alias'),
            'align'         => 'right',
            'index'         => 'alias',
        ));

        $this->addColumn('cc_number_enc', array(
            'header'    => Mage::helper('sales')->__('CC Last4'),
            'index'     => 'cc_number_enc',
        ));

        $this->addColumn('grand_total', array(
            'header' => Mage::helper('sales')->__('Order Total'),
            'index' => 'grand_total',
            'type'  => 'currency',
            'currency' => 'order_currency_code',
        ));

        $this->addColumn('status', array(
            'header' => Mage::helper('sales')->__('Status'),
            'index' => 'status',
            'type'  => 'options',
            'width' => '70px',
            'options' => Mage::getSingleton('sales/order_config')->getStatuses(),
        ));

        if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/view')) {
            $this->addColumn('action',
                array(
                    'header'    => Mage::helper('sales')->__('Action'),
                    'width'     => '50px',
                    'type'      => 'action',
                    'getter'     => 'getId',
                    'actions'   => array(
                        array(
                            'caption' => Mage::helper('sales')->__('View'),
                            'url'     => array('base'=>'adminhtml/sales_order/view'),
                            'field'   => 'order_id'
                        )
                    ),
                    'filter'    => false,
                    'sortable'  => false,
                    'index'     => 'stores',
                    'is_system' => true,
                ));
        }

        $this->addExportType('*/*/exportCsv', Mage::helper('sales')->__('CSV'));
        $this->addExportType('*/*/exportExcel', Mage::helper('sales')->__('Excel'));

        return parent::_prepareColumns();
    }

    public function getRowUrl($row)
    {
        //return $this->getUrl("*/*/edit", array("id" => $row->getId()));
    }

}
Improve this question

6 Answers 6

Reset to default
27

Solved it!

The increment_id column need to have the additional

'filter_index'=>'main_table.increment_id',

So the Grid column now looks like this:

$this->addColumn('order_id', array(
    'header' => Mage::helper('sales')->__('Order Id'),
    'align' =>'left',
    'index' => 'increment_id',
    'filter_index'=>'main_table.increment_id',
));
Improve this answer
2
  • As a comment, if you are joining tables in _prepareCollection main_table might not be the solution but the filter_index is the way to go with the table alias the index is coming from.
    – mbalparda
    Commented Mar 5, 2015 at 13:21
  • I have also the same issue and solved with the help. thanks
    – Yogendra - eCommerce Developer
    Commented Dec 5, 2016 at 10:42
8

The increment_id column need to have the additional

'filter_index'=>'main_table.increment_id',

Copy file 

app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php

to 

app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.php

Find function _prepareColumns()

Change

   $this->addColumn('real_order_id', array(
        'header'=> Mage::helper('sales')->__('Order #'),
        'width' => '80px',
        'type'  => 'text',
        'index' => 'increment_id',
    ));

To 

   $this->addColumn('real_order_id', array(
        'header'=> Mage::helper('sales')->__('Order #'),
        'width' => '80px',
        'type'  => 'text',
        'index' => 'increment_id',
        'filter_index'=>'main_table.increment_id',
    ));
Improve this answer
1
  • Hi Naveed - welcome to Magento StackExchange! Do you think this answer provides any benefit over the accepted answer over two years on? If so, could you edit your answer and provide an explanation of why? Otherwise it's just duplicate content.
    – scrowler
    Commented Feb 2, 2016 at 11:05
6

I have searched everywhere for this by I am not getting any exact result which will help me in this. But now I am with the result of why such thing is happening. It’s simple as it is saying “Integrity constraint violation: 1052 Column ‘created_at’ in where clause is ambiguous”, means it is finding another created_at field. because when we adding or joining the other table then it has also a field named as created_at. So what is the solution for this?

Any idea……………

It's simple just told Magento that created_at is of the main_table not of my custom table, how can you do so, I will tell you the full procedure.

Step 1. Find the below code in the sales order grid.php file

$this->addColumn('created_at', array(
     'header' => Mage::helper('sales')->__('Purchased On'),
     'index'  => 'created_at',
     'type'   => 'datetime',
     'width'  => '100px',
));

Step 2. in the second step just replace the code with below one.

$this->addColumn('created_at', array(
     'header'       => Mage::helper('sales')->__('Purchased On'),
     'index'        => 'created_at',
     'type'         => 'datetime',
     'width'        => '100px',
     'filter_index' => 'main_table.created_at',
));

Have you find what I have changed in the code, not you, ok let me explain. in this code I have added the following line:

'filter_index' => 'main_table.created_at',

thinking of what’s the use of the following line, here created_at column of sales order grid find created_at from the main the collection. But we are getting twice created_at in the collection. So I have added the following line that this created_at is of the main table sales_flat_order not of the other table.

That’s the process

for more info, you can visit the blog

http://www.webtechnologycodes.com/integrity-constraint-violation-1052-column-created_at-in-where-clause-is-ambiguous/

Improve this answer
5
  • It's not a good idea to edit core Magento code.
    – scrowler
    Commented Feb 2, 2016 at 11:04
  • Copy the file in local folder, then you have to change that. I am saying to edit code in core.
    – Vinay Sikarwar
    Commented Feb 3, 2016 at 6:28
  • File based overrides are also not good if you can avoid them as they block upgradability
    – scrowler
    Commented Feb 3, 2016 at 6:55
  • If you have to modify the core functionality then I thin there are only 2 possible ways, 1. Either create a module and override that class function or put a file in local and use that. But you have to taken care of for that at time of upgrade.
    – Vinay Sikarwar
    Commented Feb 3, 2016 at 7:03
  • 1
    Actually this also works for M2 and it's extremely useful for more complex joins.
    – Bartosz Kubicki
    Commented Mar 20, 2017 at 12:02
3

This error can only happen when you have joined some tables with identical column names, then you try to filter or sort by one of those columns. If we can avoid having identical column names entirely, we don't have to modify any core functionality.

One work-around for this, albeit not the most efficient, is to use a subquery to generate a temporary table, then join this table to the main table. Because you are writing this subquery, you can decide the field-names, and so you can pick ones that aren't ambiguous.

$collection->getSelect()->joinLeft(
    array('comments_history' => new Zend_Db_Expr('(
SELECT    c.parent_id as comment_parent_id, 
  group_concat(DISTINCT Concat(Datediff(c.created_at, o.created_at), ":INFO:", comment) ORDER BY c.entity_id SEPARATOR ":COMMENT:") AS comments
FROM      sales_flat_order_status_history c 
LEFT JOIN sales_flat_order o 
ON        c.parent_id=o.entity_id 
GROUP BY  c.parent_id
        )')
    ),
    'comments_history.comment_parent_id=`main_table`.entity_id',
    array(
        'order_comments_history' => 'comments_history.comments'
    )
);

The select query should have ONLY two columns: the column to join with (order Id in my case), and the column that contains the data you want. This is to reduce the chances of an ambiguous column error.

To further reduce the chance of collisions, c.parent_id is aliased to comment_parent_id. This is just in case some other module comes along and decides to join something that has a parent_id column.

Here is the subquery alone, formatted:

SELECT    c.parent_id                                                                                                                       AS comment_parent_id,
          group_concat(DISTINCT Concat(Datediff(c.created_at, o.created_at), ":INFO:", comment) order BY c.entity_id separator ":COMMENT:") AS comments
FROM      sales_flat_order_status_history c 
LEFT JOIN sales_flat_order o 
ON        c.parent_id=o.entity_id 
GROUP BY  c.parent_id

Example subquery data-set: (comment_parent_id, comments)

  1. 0:INFO:Exported order: externalId is 720521:COMMENT:8:INFO:Retrieved order status for order: 720521. Result: Tracking number is: ABC123DEF456. [103196586 : SHIPPED] New order status: COMPLETE
  2. 1:INFO:Exported order: externalId is 729023:COMMENT:6:INFO:Retrieved order status for order: 729023. Result: Tracking number is: ABC123DEF456. [104094049-black-L : SHIPPED] New order status: COMPLETE
  3. 1:INFO:Exported order: externalId is 727120:COMMENT:4:INFO:Retrieved order status for order: 727120. Result: Tracking number is: ABC123DEF456. [109129650 : SHIPPED][105130623 : SHIPPED] New order status: COMPLETE
  4. 1:INFO:Exported order: externalId is 729024:COMMENT:4:INFO:Retrieved order status for order: 729024. Result: Tracking number is: ABC123DEF456. [103094967-navy-M : SHIPPED][104090059-red-M : SHIPPED] New order status: COMPLETE
Improve this answer
3

The 'filter_index'=>'main_table.increment_id' thing everyone keeps giving as an answer didn't help for me so here's an other way to solve this issue:

In the _prepareCollection() function, just below $this->setCollection($collection); put $collection->addFilterToMap('increment_id', 'main_table.increment_id');

Improve this answer
1

filter_index will helpful in such scenario. If you have same column name in multiple tables which are used in the query. Then please use

filter_index => alias_of_table.column_name

in addColumn Function.

Improve this answer
1
  • 1
    You just posted an answer which is same as the accepted answer!
    – Prateek
    Commented Oct 15, 2015 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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