I am trying to take an export of the orders in the magento backend but I am getting this error. Can anyone let me know How can I resolve this?
a:5:{i:0;s:61:"You cannot define a correlation name 'payment' more than once";i:1;s:2483:"#0 /home/uatemrco/public_html/lib/Varien/Db/Select.php(281): Zend_Db_Select->_join('left join', Array, 'payment.parent_...', Array, NULL)
#1 /home/uatemrco/public_html/app/code/core/Zend/Db/Select.php(373): Varien_Db_Select->_join('left join', Array, 'payment.parent_...', Array, NULL)
#2 /home/uatemrco/public_html/app/code/local/Atwix/ExtendedGrid/Model/Observer.php(33): Zend_Db_Select->joinLeft(Array, 'payment.parent_...', Array)
#3 /home/uatemrco/public_html/app/code/core/Mage/Core/Model/App.php(1358): Atwix_ExtendedGrid_Model_Observer->salesOrderGridCollectionLoadBefore(Object(Varien_Event_Observer))
#4 /home/uatemrco/public_html/app/code/core/Mage/Core/Model/App.php(1337): Mage_Core_Model_App->_callObserverMethod(Object(Atwix_ExtendedGrid_Model_Observer), 'salesOrderGridC...', Object(Varien_Event_Observer))
#5 /home/uatemrco/public_html/app/Mage.php(448): Mage_Core_Model_App->dispatchEvent('sales_order_gri...', Array)
#6 /home/uatemrco/public_html/app/code/core/Mage/Core/Model/Resource/Db/Collection/Abstract.php(590): Mage::dispatchEvent('sales_order_gri...', Array)
#7 /home/uatemrco/public_html/lib/Varien/Data/Collection/Db.php(565): Mage_Core_Model_Resource_Db_Collection_Abstract->_beforeLoad()
#8 /home/uatemrco/public_html/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(951): Varien_Data_Collection_Db->load()
#9 /home/uatemrco/public_html/app/code/core/Mage/Adminhtml/Block/Widget/Grid.php(1011): Mage_Adminhtml_Block_Widget_Grid->_exportIterateCollection('_exportCsvItem', Array)
#10 /home/uatemrco/public_html/app/code/core/Mage/Adminhtml/controllers/Sales/OrderController.php(703): Mage_Adminhtml_Block_Widget_Grid->getCsvFile()
#11 /home/uatemrco/public_html/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_Sales_OrderController->exportCsvAction()
#12 /home/uatemrco/public_html/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('exportCsv')
#13 /home/uatemrco/public_html/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#14 /home/uatemrco/public_html/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#15 /home/uatemrco/public_html/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#16 /home/uatemrco/public_html/index.php(84): Mage::run('', 'store')
#17 {main}";s:3:"url";s:118:"/index.php/aksmsrrc/sales_order/exportCsv/key/9252bd3f9da8a80c055a8964c0c0be8c/internal_order_ids/4686,4685,4684,4683/";s:11:"script_name";s:10:"/index.php";s:4:"skin";s:5:"admin";}
I use an extension to manipulate and add columns to the export file that I take the code of the observer in the extension is as follows.
class Atwix_ExtendedGrid_Model_Observer
{
/**
* Joins extra tables for adding custom columns to Mage_Adminhtml_Block_Sales_Order_Grid
* @param Varien_Object $observer
* @return Atwix_Exgrid_Model_Observer
*/
public function salesOrderGridCollectionLoadBefore($observer)
{
$collection = $observer->getOrderGridCollection();
$select = $collection->getSelect();
$select->joinLeft(array('payment' => $collection->getTable('sales/order_payment')), 'payment.parent_id=main_table.entity_id', array('payment_method' => 'method'));
$select->joinLeft(array('email' => $collection->getTable('sales/order_address')), 'email.parent_id=main_table.entity_id AND email.address_type="shipping"', array('customer_email' => 'email','customer_phone' => 'telephone','shipping_street' => 'street','shipping_city' => 'city','shipping_postcode' => 'postcode','shipping_region' => 'region','shipping_country' => 'country_id'));
$select->join('sales_flat_order_item', '`sales_flat_order_item`.order_id=`main_table`.entity_id', array('skus' => new Zend_Db_Expr('group_concat(`sales_flat_order_item`.sku SEPARATOR ", ")')));
$select->group('main_table.entity_id');
}
/**
* callback function used to filter collection
* @param $collection
* @param $column
* @return $this
*/
public function filterSkus($collection, $column)
{
if (!$value = $column->getFilter()->getValue()) {
return $this;
}
$collection->getSelect()->having(
"group_concat(`sales_flat_order_item`.sku SEPARATOR ', ') like ?", "%$value%");
return $this;
}
}
and i diplay the colums using another file called grid.php with the code.
**
protected function _prepareColumns()
{
$this->addColumn('real_order_id', array(
'header'=> Mage::helper('sales')->__('Order #'),
'width' => '80px',
'type' => 'text',
'index' => 'increment_id',
));
$this->addColumn('Invoice Number', array(
'header' => Mage::helper('sales')->__('Invoice #'),
'index' => 'custom_column',
'column_css_class'=>'no-display',
'header_css_class'=>'no-display',
));
/*if (!Mage::app()->isSingleStoreMode()) {
$this->addColumn('store_id', array(
'header' => Mage::helper('sales')->__('Purchased From (Store)'),
'index' => 'store_id',
'type' => 'store',
'store_view'=> true,
'display_deleted' => true,
));
}*/
$this->addColumn('created_at', array(
'header' => Mage::helper('sales')->__('Purchased On'),
'index' => 'created_at',
'type' => 'datetime',
'width' => '100px',
));
$this->addColumn('billing_name', array(
'header' => Mage::helper('sales')->__('Bill to Name'),
'index' => 'billing_name',
));
$this->addColumn('shipping_name', array(
'header' => Mage::helper('sales')->__('Ship to Name'),
'index' => 'shipping_name',
));
$this->addColumn('Payment Method', array(
'header' => Mage::helper('sales')->__('Payment Method'),
'index' => 'payment_method',
'column_css_class'=>'no-display',
'header_css_class'=>'no-display',
));
$this->addColumn('Customer Email', array(
'header' => Mage::helper('sales')->__('Customer Email'),
'index' => 'customer_email',
'column_css_class'=>'no-display',
'header_css_class'=>'no-display',
));
$this->addColumn('Customer Phone', array(
'header' => Mage::helper('sales')->__('Customer Phone'),
'index' => 'customer_phone',
'column_css_class'=>'no-display',
'header_css_class'=>'no-display',
));
$this->addColumn('base_grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Base)'),
'index' => 'base_grand_total',
'type' => 'currency::NO_SYMBOL',
'currency' => 'base_currency_code',
));
$this->addColumn('grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Purchased)'),
'index' => 'grand_total',
'type' => 'currency::NO_SYMBOL',
'currency' => 'order_currency_code',
));
$this->addColumn('street', array(
'header' => Mage::helper('sales')->__('Shipping Street'),
'index' => 'shipping_street',
'column_css_class'=>'no-display',
'header_css_class'=>'no-display',
));
$this->addColumn('city', array(
'header' => Mage::helper('sales')->__('Shipping City'),
'index' => 'shipping_city',
'column_css_class'=>'no-display',
'header_css_class'=>'no-display',
));
$this->addColumn('postcode', array(
'header' => Mage::helper('sales')->__('Shipping Postcode'),
'index' => 'shipping_postcode',
'column_css_class'=>'no-display',
'header_css_class'=>'no-display',
));
$this->addColumn('region', array(
'header' => Mage::helper('sales')->__('Shipping Region'),
'index' => 'shipping_region',
'column_css_class'=>'no-display',
'header_css_class'=>'no-display',
));
$this->addColumn('country', array(
'header' => Mage::helper('sales')->__('Shipping Country Id'),
'index' => 'shipping_country',
'column_css_class'=>'no-display',
'header_css_class'=>'no-display',
));
$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'=>'*/sales_order/view'),
'field' => 'order_id',
'data-column' => 'action',
)
),
'filter' => false,
'sortable' => false,
'index' => 'stores',
'is_system' => true,
));
}
$this->addRssList('rss/order/new', Mage::helper('sales')->__('New Order RSS'));
$this->addExportType('*/*/exportCsv', Mage::helper('sales')->__('CSV'));
$this->addExportType('*/*/exportExcel', Mage::helper('sales')->__('Excel XML'));
return parent::_prepareColumns();
}
**
Best Answer
The error is related to
join
query you are using. If you debug the error it will take you to theZend_Db_Select::_join()
function.The arguments passed to this function are
($type, $name, $cond, $cols, $schema = null)
This function checks for correlation names already added to the query. If any duplicate is found, it will throw exception.
Try with below code in your
salesOrderGridCollectionLoadBefore
function.