5
Custom Magento Grid with Ambiguous Column on Filter
4 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!
4 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?
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.