[FIXED] Yii two-level "with" in Relational Active Record

Issue

I have three tables:

1. tbl_tag
2. tbl_post
3. tbl_post_tag(post_id, tag_id)
4. tbl_users.

Each Post has multiple Tags and each Tag has multiple Posts. (MANY_MANY)

Each Post has only one User and each User has multiple Posts.

I use tbl_post_tag to record the relations between Post and Tag.

Now I'd like to retrieve all the Posts by tag_id (like show all posts and corresponding user with tag_id = 1)

I really don't know how to do this.

My guess is to use Tag::model()->with("post")->findByPk(1), but does this retrieve Users at the same time?

Or is there any better way to do this job?

Thanks!

Solution

Just to clarify, you want to get all of the Posts (and the User who posted it) with a single Tag?

Set up the following relation in the Tag model (it should be there already if you generated your code with Gii or yiic):

public function relations()
{
  return array(
    'posts'=>array(self::MANY_MANY, 'Post', 'tbl_post_tag(post_id, tag_id)'),
  );
}

This will allow you to get all of the Posts with a Tag like so:

$myTag = Tag::model()->findByPk(1);
$posts = $myTag->posts;

This does not get the User though. If you want the User of each Post, do something like this (assuming you have the right relations set up between user and post in the Post model):

foreach ($myTag->posts as $post) {
  $theUser = $post->user;
  $theUsersName = $post->user->name;
}

The with() command you mentioned is about efficiency, as it just pre-loads the relational requests, instead of waiting to do each one when you call it. You can read more about it here and in the Class Reference.

This may not be what you are looking for though. Your question was not terribly clear.

If you are trying to do a big JOIN query, you will need to go ahead and write a JOIN query probably. It depends on what you are doing though. It's not too bad to add JOIN and SELECT conditions to a findall() statement.

Good luck!

Answered By - thaddeusmt