Magento – Admin Grid Filtering on Joined Column

collection;gridjoin;

I have an admin grid that I'm using, and pulling in custom model and displaying. Everything works fine, and all columns can filter/sort, except when I try to use a column that is not inherent to my custom model. Specifically, I'm trying to use a join from my custom model to another custom table, and then from there to another custom table, all using flat tables.

I've included my _prepareCollection() and my _prepareColumns() functions for reference. I'm doing my joins in my _prepareCollection() function, and this is an acceptable solution for my grid if I can get filtering working, but it would be preferable to move the joins into my custom model collection instantiation, so this custom column is always accessible when using my model.

It may also make sense to add a function in my model collection that specifically loads the contents of the joins, but is separate from instantiation. This may lower the overhead in times when I'm interacting with my model but do not need the extra column.

I'm looking for a suggestion as to which of the latter two solutions is a better one, and a way to implement it, or if this cannot be provided, a way to get filtering working when my joins are within the grid.

Thank you, help appreciated!

protected function _prepareCollection()
{
    $collection = Mage::getModel('mymodel/post')
        ->getCollection()
        ->addFieldToFilter( 'post_type', array('eq' => 'post') );

    // Join to additional wordpress tables so we have all the data we need
    $collection
        ->getSelect()
        ->join(
            'tag_relationships',                                                                                      // Alias
            'id = tag_relationships.object_id',                                                                       // On clause
            array('tag_ids' => 'tag_relationships.tag_id')                                                 // Column
        );

    $collection->getSelect()->join(
        'tags',                                                                                                   // Alias
        'tags.tag_id = tag_relationships.tag_id',
        array('tags' => new Zend_Db_Expr('group_concat(`tags`.name SEPARATOR ", ")') )  // Group concat
    );


    $collection->getSelect()->group('main_table.id');


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

protected function _prepareColumns()
{

    // Save the ID of the product we're editing
    $productId = Mage::app()->getRequest()->getParam('id');
    $map = Mage::getModel('mymodel/post')->load($productId);

    $helper = Mage::helper('company_mymodel');
    $currency = (string) Mage::getStoreConfig(Mage_Directory_Model_Currency::XML_PATH_CURRENCY_BASE);

    $this->addColumn('itemCheckbox', array(
        'index'      => 'ID',
        'type'       => 'checkbox',
        'width'      => 20,
        'sortable'   => false,
        'field_name' => 'map[]',
        'values'     => $map->getRelatedProjects($productId)
    ));

    $this->addColumn('id', array(
        'header' => $helper->__('ID'),
        'index'  => 'ID'
    ));


    $this->addColumn('tags', array(
        'header' => $helper->__('Terms'),
        'index'  => 'tags',
        'filter_index' => 'tags',
        'filter_condition_callback' => array($this, 'tagsFilter')
    ));


    $this->addExportType('*/*/exportRelatedCsv', $helper->__('CSV'));
    $this->addExportType('*/*/exportRelatedXml', $helper->__('XML'));

    return parent::_prepareColumns();
}

Best Answer

I recommend to use Magento methods over Zend_db_Select methods, because magento is doing book keeping, what is joined and which fields are available.

Beside this, if it is not just joined, you need to define either a filter_index or the filter callback you seem to have

Related Solutions

Magento – add column to sales/orders grid

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.

Admin Order Grid Custom Fields – Filtering Issue

Hi Just add filter_index in increament_id field and it may works

        'filter_index' => 'main_table.increment_id',

and now code

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

));

Best Answer

Related Solutions

Magento – add column to sales/orders grid

Admin Order Grid Custom Fields – Filtering Issue

Related Topic