This is a duplicate of the question Add Column to a grid (observer) - Column ‘store_id’ in where clause is ambiguous issue, here I explained how to add a column to the sales/order grid the right way (without file copy or a rewrite).
The sales/order grid uses the table sales_flat_order_grid to display its information, this is done to list all orders without adding joins and speeding-up the loading. In order to add a column to the grid you would need to add a field to this table and update the information in that field when an order is created/updated. Below the steps needed to add this to the grid:
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_city',
'varchar(255) 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_city',
'customer_city'
);
// Now you need to fullfill existing rows with data from address table
$select = $this->getConnection()->select();
$select->join(
array('address'=>$this->getTable('sales/order_address')),
$this->getConnection()->quoteInto(
'address.parent_id = order_grid.entity_id AND address.address_type = ?',
Mage_Sales_Model_Quote_Address::TYPE_BILLING
),
array('customer_city' => 'city')
);
$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_city</columnId>
<arguments module="sales" translate="header">
<header>Customer City</header>
<index>customer_city</index>
<type>text</type>
<width>200</width>
</arguments>
<after>shipping_name</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>
The last file needed is needed to update the data 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.
/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(
'customer_city',
'sales/order_address',
array('billing_address_id' => 'entity_id'),
'city'
);
}
}
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.
Please try with this:
$collection
->getSelect()
->join(
array('so' => $this->getTable('sales/order')),
'main_table.order_id = so.entity_id',
array('so.grand_total')
)
->join(
array('soa' => $this->getTable('sales/order_address')),
"(main_table.order_id = soa.parent_id AND soa.address_type = 'shipping')",
array('soa.*')
);
replace order_id with the column name that you used in your table for the order ID.
Also you can view/read this - http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/using_collections_in_magento .
Best Answer
You are joining one table to another with 1-N relationship.
It means for one row from the table A you get N rows from the table B
At the same time you fetch values to the magento collection, that use unique row ID and it throws the error.
Probably you need to group values, so if it is page views you can group by page_id and calculate count with aggregate function.
Note, that with the
group bystatement in the original SQL query you need custom approach for the pager as the default wont work. So sometimes it easier (but not faster or better) to use subqueries.