Relational query and getting data from related model

Yii 1.1 General Discussion
1

Hi, in my database I have the following table


Post

 - id (PK)

 - contents


Comment

 - id (PK)

 - post_id (FK)

 - user_id (FK)

 - comment


User

 - id (PK)

 - name

Relations is as follow


Post

 - 'reviews' => array(self::HAS_MANY, 'Comment', 'post_id'),


Comments

 - 'user' => array(self::BELONGS_TO, 'User', 'user_id'),

 - 'post' => array(self::BELONGS_TO, 'Post', 'post_id'),


User

 - 'reviews' => array(self::HAS_MANY, 'Comment', 'user_id'),

In my PostController, I have the following code to get the comments,


$model=Post::model()->findByPk($id);

$comments = $model->comments;

I can get all the comments out but at the same time, how do I get the user’s name as well?

Thanks.

2

check MANY_MANY section in this topic :wink:

3

Dear Joe

The following code will fetch all comments and names of users who made those comments for a particular post.




$model=Post::model()->findByPk($id);

$comments = $model->reviews;//not $model->comments. This is in accordance with your relations declarations.

foreach($comments as $comment)

{

echo $comment->user->name;

echo "</br>";

echo $comment->comment;

}

Regards.

4

LAZY LOADING




$model=Post::model()->findByPk($id);  // #1

$comments = $model->comments;         // #2

foreach($comments as $comment)

{

    $user = $comment->user;           // #3

    echo $user->name;

}

It uses ‘comments’ relation of Post in #2 and ‘user’ relation of Comment in #3 with lazy loading approach.

It will cost 1(#1) + 1(#2) + N(#3) queries.

And, as you may know, you can write like this without the local variables of $comment and $user:




$model=Post::model()->findByPk($id);

foreach($model->comments as $comment)

    echo $comment->user->name;

It also costs 1 + 1 + N queries.

EAGER LOADING




$model=Post::model()->with('comments', 'comments.user')->findByPk($id);  // #1

$comments = $model->comments;         // #2

foreach($comments as $comment)

{

    $user = $comment->user;           // #3

    echo $user->name;

}

OR




$model=Post::model()->with('comments', 'comments.user')->findByPk($id);

foreach($model->comments as $comment)

    echo $comment->user->name;

This is eager loading approach.

It will cost just one query for #1.

In #2 and #3, there will be no execution of query, just accessing the already retrieved values.

You will get the same results as the lazy loading approach, but usually this is more efficient.

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