Magento – How to do multiple join query in magento

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.

The error shows that main_table.volume_id does not exist, because main_table is the alias for the product entity table which does not contain a column volume_id.

You have defined volIds as alias for your volume_product association table. So you need to change it

Change from

$collection->getSelect()->join(array('volumes' => $volsTable),
    'main_table.volume_id = volumes.entity_id', array('volume_name'));

to

$collection->getSelect()->join(array('volumes' => $volsTable),
    'volIds.volume_id = volumes.entity_id', array('volume_name'));