Admin Grids - Filtering A Joined Column

Posted . Visible to the public.

Often, you'll want to add a column to a collection used in a grid, and to do so you'd use _prepareCollection():

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $this->setCollection($collection);

    $collection->getSelect()
       ->joinLeft(
           array('table_alias' => 'some_long_table_name'),
           'main_table.something = table_alias.something',
           array('some_column' => 'table_alias.some_column')
       );

    return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();
}

protected function _prepareColumns()
{
    parent::_prepareColumns();

    $this->addColumnAfter(
        'some_column',
        [
            'header' => Mage::helper('sales')->__('Some Column'),
            'index' => 'some_column',
        ],
        'add_after_column'
    );
}

This works fine until you try and filter the grid, at which point you'll get an unknown column. The issue is that Magento doesn't know what column some_column is, so it just refers to it in the SQL statement as some_column, which doesn't work, because column aliases don't work in WHERE conditions.

To fix the problem, you need to tell Magento what 'real' column it is in the collection prepation, like so:

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $this->setCollection($collection);

    $collection->getSelect()
       ->joinLeft(
           array('table_alias' => 'some_long_table_name'),
           'main_table.something = table_alias.something',
           array('some_column' => 'table_alias.some_column')
       );
    $collection->addFilterToMap('some_column', 'table_alias.some_column');

    return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();
}

Thanks to this post Show archive.org snapshot for the tip.

Mike Whitby
Last edit
Mike Whitby
Posted by Mike Whitby to Magento (2015-09-16 11:13)