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:

distributor

  • id
  • street
  • city
  • zip
  • country_id
  • region_id

locate_country_region

  • region_id
  • country_id
  • code
  • location

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

protected function _prepareCollection() {
  $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.

 

Hope this works for you! Many thanks for referring this blog.