Problem with BELONGS_TO relation

Yii 1.1 General Discussion
1

Hi there,

I have Products model with the following table in db:

id | groupId | etc.,

and ProductsGroups model with:

id | name

now, in my Products model Im creating relation like this:


'group'=>array(self::BELONGS_TO, 'ProductsGroups', 'groupId')

in ProductsGroups:


'products'=>array(self::HAS_MANY, 'Products', 'groupId')

the problem is:

when i’m trying to find all product records using ‘with’ method:


$products = Products::model()->with('group')->findAll();

im getting CDbException with the following message:

[sql]CDbCommand nie zdołał wykonać polecenia SQL: SQLSTATE[42000]: Syntax error or access violation: 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.id AS t1_c0, group.name AS t1_c1 FROM shop_products LEFT OUTER ’ at line 1[/sql]

What am i doing wrong here? Because in other controllers with different models i also used this kind of relations with success

2

Did you try the same SQL statement in phpmyadmin? I think group is a reserved word in MySQL and must be written as group. So this might be a little bug: table aliases should be surrounded by backticks.

3

‘group’ is a reserved word (used in GROUP BY). Quote it with backticks (or whatever your DB supports for quoting) or use another name.

4

table: event (class Event) …

id_event


id_producer


id_transport


id_destination


name_event


.....

table: usuario (class User)

id_usr


id_company


id_name


id_lastname


.....

table: Company (class Company)

id_company


name


address


.....

the model created automatically and gave me these relation for event :

‘id_producer’ => [color="#0000FF"]array[/color]([color="#000080"]self[/color]::BELONGS_TO, ‘usuario’,‘id_producer’),

‘id_transport’ => array(self::BELONGS_TO, ‘usuario’, ‘id_transport’),

…

I was trying to use grid.CGridView so i changed the relation with this:

‘producer’ => array(self::BELONGS_TO, ‘Company’, ‘id_producer’),

…

for view

array(‘header’ => ‘Producer’,‘name’=>‘id_producer.address’,‘htmlOptions’=>array(‘width’=>‘60px’)),

and works just for view but not for update

My question is :

what could be the right relation between to get from model event the attribute of modal company