Multi Relation Problem

Yii 1.1 General Discussion
1

Hi everyone

I have a strange issue in cgridview with sorting or searching a related AR model

If the related field is first lever everything works fine

for example $criteria->compare(‘category.category_id’, $this->rel_catid, true); (category is related with product)

but in this case occurs error "CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found"

$criteria->compare(‘category.groups.group_id’, $this->rel_group, true); (category is related with product, and group related with category)

in the message error I can see only one LEFT OUTER JOIN with category but I do not see JOIN with group table.

I am sure the relation exist (I created by myself) also there is no problem with echo $modelProduct->category->groups->group_id in view file.

The same problem occurs when I sorting the specific field in CGridView

What I am missing?

Thanks

2 



$criteria->compare('groups.group_id', $this->rel_group, true);

:)

3

Hi Konapaz

Can you post your model code

4

Hi softark,

The main model is product so the category (a Relation of product) is nessesary

$criteria->compare(‘category.groups.group_id’, $this->rel_group, true);

5

You are expected to prepend the table alias to ‘group_id’ here. And the table alias for ‘category.groups’ relation is ‘groups’ not ‘category.groups’.

6

Hi alriz

for product model


  public function relations() {

        return array(

            'category' => array(self::BELONGS_TO, 'category', 'product_id'),

        );

    }


public function search() {

....

$criteria->compare('category.groups.group_id', $this->rel_group, true);

...

...

 return new CActiveDataProvider($this, array(

            'criteria' => $criteria,

            'sort' => array(

            'rel_product_s_desc' => array(

                        'asc' => 'category.groups.group_id',

                        'desc' => 'category.groups.group_id DESC',

                    ),

            ));

   }

and for category model


  public function relations() {

        return array(

            'groups' => array(self::BELONGS_TO, 'group', 'category_id'), 

        );

    }

(The schema is for testing and not for production site)

7

This is kinda complicated I am not saying its impossible but it is hard to implement since you sorting products by product -> category -> groups this is some heavy stuff

EDIT: infact it is impossible if am not mistaken

I would really love to see how you tackle this problem

8

And also for the ‘ORDER’ clause:




return new CActiveDataProvider($this, array(

            'criteria' => $criteria,

            'sort' => array(

            'rel_product_s_desc' => array(

                        'asc' => 'groups.group_id',

                        'desc' => 'groups.group_id DESC',

                    ),

            ));

   }
9

This is just a nested relation of BELONGS_TO->BELONGS_TO, so it’s quite easy to sort on that. :)

10

both of two ways generate a similar error

The generated sql query has exactly one "LEFT OUTER JOIN" with the first table

but in properly way have to generates double LEFT OUTER JOIN like

LEFT OUTER JOIN category ON ‘t.product_id=category.id’ LEFT OUTER JOIN groups ON t.category_id=group.id

LEFT OUTER JOIN category ON ‘t.category_id=category.id’ LEFT OUTER JOIN groups ON category.groups_id=group.id

11

would you mind explaining how is it BELONGS_TO->BELONGS_TO

If I am not mistaken he wants to sort products based on category groups and that groups category is many to many its not simple one to many

EDIT; softark my bad infact it is one to one on groups -> categories

12

Can you show me the full code of search() method?

13

quick question does yii even handle the level 2 relationship in compare

14

“groups” is a bad name, I think. :)

15

exactly my point he has rethink his db schema

16

Yes, of course.




$criteria->with = array('catetory', 'category.group');

$criteria->compare('t.something', $this->something, true);

$criteria->compare('category.name', $this->category_name, true);

$criteria->compare('group.name', $this->group_name, true);

@KonApaz

Did you include ‘category.groups’ in ‘with’?

17

Thank you very match softark!

You were prompt me to check my code!! :)

I had


$criteria->with = array('category')

in search method

but with double relation I have to do


$criteria->with = array('category'=>array('with'=>'groups'))

I give you fairly a vote :)

18

As I said this is a testing schema not a production one ;)

19

thats is not level 2 that is just joined tables. you cant sort products on product.category.group you had to join group with category first

20

I believe that the notation of "relation.child_relation" joins the level 2 related table.

Once joined, you can filter and/or sort on that using the table alias … that is ‘child_relation’, not ‘relation.child_relation’.

[EDIT]

For reference:

CActiveRecord::with() http://www.yiiframework.com/doc/api/1.1/CActiveRecord#with-detail

Performing Relational Query

http://www.yiiframework.com/doc/guide/1.1/en/database.arr#performing-relational-query