5
Custom Magento Grid with Ambiguous Column on Filter
13 Comments | Posted by Will Wright in Magento, PHP
The Problem
You’ve built a custom grid and the source collection has an ambiguous column in the where clause when applying a filter.
The Solution
I recently ran into this problem while I was building a custom grid. The situation should be familiar to many of you. I have a custom table storing addresses; for the region and country I store a region_id and country_id, respectively. For display of the grid however I want to do a lookup to show the user the friendly names for both region and country. To accomplish that I do an inner join on the original collection which yields the full dataset.
However, this also introduces a douplicate column into the collection which was causeing an ambigous column error in my SQL statement whenever I applied a filter (a WHERE clause). Lets take a look at an example.
My tables:
retailer
- id
- street
- city
- postcode
- country_id
- region_id
directory_country_region
- region_id
- country_id
- code
- default_name
Now lets take a look at my _prepareCollection method.
protected function _prepareCollection() {
$collection = Mage::getResourceModel('spinonesolutions_storelocator/retailer_collection');
$collection->getSelect()
->join(array('dcr' => 'directory_country_region'),'dcr.region_id = main_table.region_id')
->reset(Zend_Db_Select::COLUMNS)
->columns(array('id','name','street','city','country_id'))
->columns(array('default_name'),'dcr');
$this->setCollection($collection);
return parent::_prepareCollection();
}
This will result in a collection containing all the data that I need for a friendly presentation. However, if I want to filter by country_id (which I do!) then I’m going to get an ambiguous column error in my SQL if I simply add “WHERE country_id = ?”. That’s because country_id is in both tables.
The solution is the “filter_index” property. Here’s a snippet from _prepareColumns which generates the country column and uses filter_index.
$this->addColumn('country_id', array(
'header' => Mage::helper('spinonesolutions_storelocator')->__('Country'),
'width' => '100px',
'index' => 'country_id',
'filter_index' => 'main_table.country_id',
'type' => 'country', ));
Now when the SQL statement is generated the ambiguous column error will be fixed since I’ve provided enough specificity. Simple!
Questions and comments welcome!
13 Comments for Custom Magento Grid with Ambiguous Column on Filter
Ruben Marques | January 19, 2010 at 3:39 am
nikolay | January 29, 2010 at 9:31 am
This code:
$query = ”
SELECT
users.user_id, users.email,
users.first_name, users.last_name,
users.last_login, users.created, users.status,
countries.name AS country, provinces.name AS province, cities.name AS city, districts.name AS district,
properties_count.properties_count
FROM users
LEFT OUTER JOIN countries ON countries.country_id = users.country_id
LEFT OUTER JOIN provinces ON provinces.province_id = users.province_id
LEFT OUTER JOIN cities ON cities.city_id = users.city_id
LEFT OUTER JOIN districts ON districts.district_id = users.district_id
LEFT OUTER JOIN (
SELECT user_id, count(*) AS properties_count
FROM properties
GROUP BY user_id
) AS properties_count ON properties_count.user_id = users.user_id
“;
id giving me the same error! Can you help me?
Will Wright | February 1, 2010 at 7:36 am
My guess is that it’s the left join on the sub-query that’s causing this error:
LEFT OUTER JOIN (
SELECT user_id, count(*) AS properties_count
FROM properties
GROUP BY user_id
) AS properties_count ON properties_count.user_id = users.user_id
Does the query work without this join?
zzandry | March 31, 2010 at 11:14 am
Thanks guy, it’s a big help for me
OSdave | July 10, 2010 at 1:49 pm
thanks man, this was really helpfull. I hope you’ll find time to write some more interesting magento snippets like this one.
razvantim | January 22, 2011 at 4:48 am
thanks for the quick tip
Mark | March 10, 2011 at 2:28 am
wowwww, it worked..
Thanks alot!!!
writemcodeboy | September 14, 2011 at 4:38 am
This helped me a lot.
In my case I had ambiguous column for the mass action.
My resolve was as follows :
protected function _prepareMassaction()
{
$this->setMassactionIdField(‘main_table.entity_id’);
Note : table reference (‘main_table’).
Thanks for the direction!
Rao | January 24, 2012 at 3:14 am
hy there. i love the title of your site.
thanks for your great work (sharing is caring).
well done care taker.
i was seriously looking for such solution.
this is my query:
“SELECT main_table.*, m.cat_title FROM linkcategory AS main_table LEFT JOIN linkcategory AS m ON main_table.parent_cat_id = m.cat_id”
what i was trying to achieve , to get the name of parent categories and make them filterable (naturally) but i after trying a lot. solved it in doing (type => ‘optons’) way. this column cat_title stays ambiguous , no matter what.
But i found very interesting the properties that are available for preparing columns ($this->addColumn(”,array(
” => ”
what more properties can be used here? please point me to the direction.
))
thank you.
Hi Will, it worked for me but with a little change.
Instead of
$collection = Mage::getResourceModel(‘spinonesolutions_storelocator/retailer_collection’);
I used
$collection = Mage::getModel(‘spinonesolutions_storelocator/retailer_collection’)->getCollection();
If this change isn’t done, a warning will appear saying that getSelect() is not defined.