Ttdfjt

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

 

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

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

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/

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)

2050. 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


2051. 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


2052. 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


2053. 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