AR 'second-level' join not working

Yii 1.1 General Discussion
1

Hi all,

There’s something I don’t understand with AR / $criteria->with.

take the table structure from

http://www.yiiframework.com/doc/guide/1.1/en/database.arr

I’m trying to make CGridView of the Post Model including user and category:

I’d doing the following search:


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

...

$criteria->with(array(user, category))

$criteria->compare('user.name',$this->search_username,true)

...

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

I have the rules set correctly in my Post Model - and I can display related data.

However, when I try to display the CGridView I get an SQL error saying category.name is unknown. And indeed, category not left outer join’ed.

But User gets properly join’ed

I also tried to create a post_category model containing a category relation.

Then, in Post, if I set up


$criteria->with(array(user, post_category.category))

I see that post_category is join’ed, but category is not :(

It seems to me that the ‘second level’ join is not working as I would expect it. Any ideas what I can do to ensure category gets join’ed ?

2

Hi all,

I solved this by adding


$this->_finder->joinAll=true;

in


yii/framework/db/ar/CActiveFinder.php:438

before


$this->buildQuery($query);

Bug report submitted

3

Um, it’s not a bug of Yii’s relational active record.

You should not touch the core code of Yii. You can use ‘together’ option instead.

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

You had to join the related table in the query of the main table, because you wanted to filter the result by the column in the related table. But it’s not always so. Hence the option ‘together’ comes in.

Please check this wiki article:

http://www.yiiframework.com/wiki/527/relational-query-lazy-loading-and-eager-loading-with-and-together

4

As I understand it, using together will always load the related model no matter what. This has an impact on performance.

I want to do a specific, targeted search in a related table. I’m using ->with() for directly related table - which works fine. Is there a reason that ->with() shouldn’t work for 2nd level related tables ?

That’s what I understand is documented in

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

And by adding the above stated line AR performs the same way for 2nd or higher level joins just as it does for 1st level joins (where, by the way, the line I recommend adding is present)

5

You could set ‘select’ to false to avoid loading the related model.

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

It doesn’t matter whether the relation is 1st level or 2nd level. What matters is whether it is BELONGS_TO/HAS_ONE or HAS_MANY/MANY_MANY.

Did you take a time to read the wiki that I mentioned?

http://www.yiiframework.com/wiki/527/relational-query-lazy-loading-and-eager-loading-with-and-together

Do you mean adding




$this->_finder->joinAll=true;

in yii/framework/db/ar/CActiveFinder.php:438 ?

Don’t do it. It will break your existing working code. This is equivalent to setting ‘together’ to true in each and every related query. It certainly should not be what you want.