Magento: Filtering a getCollection is not working

My getCollection code is providing the wrong query string (i think).

I have a table called banner which I can load all records from easy enough. When I try to filter it I'm getting errors.

Here is the code:

开发者_如何学JAVA$banner = Mage::getModel('banner/banner')->getCollection()->addFieldToFilter('group', array('eq'=>'search_group'));

The page crashes and I get this error:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group = 'search_group')' at line 1";i:1;s

As you can see it seems like the code is messing up the quotes after group.

'group = 'search_group')'

Can anyone advice on how to fix this?

Thanks,

Billy

group is an SQL keyword. If group is also an attribute name you'll need to escape it somehow. Try using backticks (the typically unused key below Esc).

$banner = Mage::getModel('banner/banner')
    ->getCollection()
    ->addFieldToFilter('`group`', 'search_group');

You're misinterpreting the error text.

 to use near 'group = 'search_group')' 

The outer quotes are the error messages way of blocking something off as code. This probably would have been clearer

 to use near [group = 'search_group')]

It's always best to look at the select your collection is using (assuming a a non-eav collection here, given your Module Creator style class alias) and try running in directly in your MySQL client (PHPMyAdmin, Command Line app, Query analyzer, Sequel Pro, etc.)

header('Content-Type: text/plain');
echo (string) $widget->getSelect();
echo "\n";
var_dump ( (string) $widget->getSelect());
Mage::Log((string) $widget->getSelect());
exit;

Seeing the entire query in context usually makes spotting an error easier.

One way will be:-

Write the following function in your collection class:-

public function setGroupBy($group)
{
    $this->getSelect()->group($group);
    return $this;
}

Then you can use it like:-

$banner = Mage::getModel('banner/banner')->getCollection()->setGroupBy('search_group');

Hope this helps.