Fixing Ambiguity in Magento Grids: A Guide to Custom Column Filters

We have situations with maintaining a custom Magento grid by adding one or more custom columns for retrieving meaningful and expanded details. Suppose we have built a custom grid and the source collection has an ambiguous column in the where / group by clause when applying a filter option i.e., we may have stuck with the following exception in Magento backend.

SQLSTATE [23000]: Integrity constraint violation: 1052 Column 'store_id' in where
 clause is ambiguous
../lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo
->_execute(Array)
../lib/Zend/Db/Statement.php(300): Varien_Db_Statement_Pdo_Mysql
->_execute(Array)
../lib/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement
->execute(Array)
../lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract
->query('SELECT DISTINCT...', Array)
../lib/Varien/Db/Adapter/Pdo/Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract
->query('SELECT DISTINCT...', Array)
../lib/Zend/Db/Adapter/Abstract.php(825): Varien_Db_Adapter_Pdo_Mysql
->query(Object(Varien_Db_Select), Array) ....

Reason / Illustration

Suppose we have a custom table storing addresses for the region and country. We store a region_id and country_id respectively. For display of the grid however, we want to do a lookup to show the admin user-friendly names for both region and country. To accomplish that we do an inner join database operation on the original collection that yields the full dataset.
This (join) may cause a duplicate column into the collection which has an ambiguous column error in my SQL statement whenever we applied a filter with WHERE / GROUP BY clause.
Let’s discuss with an example table structure.
Tables we have:

1. distributor
2. id
3. street
4. city
5. zip
6. country_id
7. region_id
8. locate_country_region
9. region_id
10. country_id
11. code
12. location

Now let’s take a look at _prepareCollection method in Grid.php file.

 $collection = Mage::getResourceModel('egits_locator/distributor_collection');
  $collection->getSelect()
    ->join(array('lcr' => 'locate_country_region'),
'lcr.region_id = main_table.region_id')
    ->reset(Zend_Db_Select::COLUMNS)
    ->columns(array('id','name','street','city','country_id'))
    ->columns(array('location'),'lcr');
  $this->setCollection($collection);
  return parent::_prepareCollection();
}

This will result in a collection containing all the data that we need for a friendly presentation. However, if we want to filter by country_id , then we’ll get an ambiguous column error in my SQL if we simply add “WHERE country_id = ?”. That’s because country_id is in both tables.

Solution

The solution is the “filter_index” property. Here we go a code snippet from _prepareColumns method in Grid.php file itself, which generates the country column and uses filter_index.

$this->addColumn('country_id', array(
  'header' => Mage::helper('egits_locator')->__('Country'),
  'width' => '100px',
  'index' => 'country_id',
  'filter_index' => 'main_table.country_id',
  'type' => 'country', ));

So when the SQL statement is generated the ambiguous column error will be fixed since we have provided the correct filter reference identity for the corresponding column to be filtered.

I hope this works for you! Many thanks for referring this blog.