MANY_MANY relationship failed "with"

Yii 1.1 General Discussion
1

So, it’s the second time I encounter this situation.

models/Links.php




        public function relations() {

                return array(

                   'groups' => array(self::MANY_MANY, 'Groups', 'VnGroupsLinks(VnLinksId,VnGroupsId)'),

                   'categories' => array(self::MANY_MANY, 'Categories', 'VnLinksCategories(VnLinksId,VnCategoriesId)'),

                );

        }

models/Categories.php




        public function relations() {

                return array(

                   'categories' => array(self::MANY_MANY, 'Links', 'VnLinksCategories(VnCategoriesId,VnLinksId)'),

                );

        }

models/Groups.php




        public function relations() {

                return array(

                   'users' => array(self::BELONGS_TO, 'Users', 'GroupsId'),

                   'links' => array(self::MANY_MANY, 'Links', 'VnGroupsLinks(VnGroupsId,VnLinksId)'),

                );

        }

views/categories/_someview.php




 $dataProvider = new CActiveDataProvider('Links', array(

        'criteria'=>array(

                'with' => array('categories','groups'),

                'condition'=> 'categories.id = ' . $model->id,

                'order' => 'Nom',

        ),

 ));

When I leave the "condition" on, I get this error message:




 SQLSTATE[42S22]: Column not found: 1054 Unknown column 'categories.id' in 'where clause'. The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`Nom` AS `t0_c1`, `t`.`Link` AS `t0_c2`, `t`.`Seq` AS `t0_c3`, `t`.`Icon` AS `t0_c4`, `t`.`TargetFrame` AS `t0_c5` FROM `VnLinks` `t` WHERE (categories.id = 1) ORDER BY Nom LIMIT 10

But when I leave it off, it works great.

The problem seems to be this:




:03:14.150868 	trace 	system.db.ar.CActiveRecord 	

Links.findAll()

in /data/protected/extensions/bootstrap/widgets/TbGridView.php (48)

in /data/protected/views/categories/categorylinks.php (25)

in /data/protected/views/categories/view.php (32)


16:03:14.151906 	trace 	system.db.CDbCommand 	

Querying SQL: SELECT `t`.`id` AS `t0_c0`, `t`.`Nom` AS `t0_c1`, `t`.`Link`

AS `t0_c2`, `t`.`Seq` AS `t0_c3`, `t`.`Icon` AS `t0_c4`, `t`.`TargetFrame`

AS `t0_c5` FROM `VnLinks` `t`  ORDER BY Nom LIMIT 10

in /data/protected/extensions/bootstrap/widgets/TbGridView.php (48)

in /data/protected/views/categories/categorylinks.php (25)

in /data/protected/views/categories/view.php (32)


16:03:14.155771 	trace 	system.db.CDbCommand 	

Querying SQL: SELECT `t`.`id` AS `t0_c0`, `categories`.`id` AS `t1_c0`,

`categories`.`CategoryName` AS `t1_c1`, `categories`.`Seq` AS `t1_c2`,

`categories`.`pos_x` AS `t1_c3`, `categories`.`pos_y` AS `t1_c4` FROM

`VnLinks` `t` LEFT OUTER JOIN `VnLinksCategories` `categories_categories`

ON (`t`.`id`=`categories_categories`.`VnLinksId`) LEFT OUTER JOIN

`VnCategories` `categories` ON

(`categories`.`id`=`categories_categories`.`VnCategoriesId`)  WHERE

(`t`.`id` IN (93, 101, 27, 74, 75, 76, 95, 16, 134, 2))

in /data/protected/extensions/bootstrap/widgets/TbGridView.php (48)

in /data/protected/views/categories/categorylinks.php (25)

in /data/protected/views/categories/view.php (32)

Why is there a query like this?

[b]Querying SQL: SELECT t.id AS t0_c0, t.Nom AS t0_c1, t.Link

AS t0_c2, t.Seq AS t0_c3, t.Icon AS t0_c4, t.TargetFrame

AS t0_c5 FROM VnLinks t ORDER BY Nom LIMIT 10[/b]

Thanks!

2

Try with


$dataProvider = new CActiveDataProvider('Links', array(

        'criteria'=>array(

                'with' => array('categories','groups'),

                'together' => true,

                'condition'=> 'categories.id = ' . $model->id,

                'order' => 'Nom',

        ),

 ));

This is how Yii writes the SQL queries. Apart that, the ‘together’ property will tell Yii to include JOINs for the related tables specified in the ‘with’ property

3

Also, you may want to avoid directly concatenating variables into your queries, and rather bind them. In other words, change:


…

'condition'=> 'categories.id = ' . $model->id,

…

into


…

'condition' =>  'categories.id = :modelId',

'params' => array(':modelId' => $model->id),

…
4

Thank you, it works like a charm!

I was aware of the pdo thing, it’s just that I’m lazy and I need to take the habit. I will try harder!

Thanks and have a great day.