Need help with this Query

I have 3 models (User, Post and favorite) with relations as mentioned below.

User has many Posts

User has many Favorites

Favorite belongs to User

[b]

[/b]

I need to list all favorite posts for a given user on his/her profile and query mentioned below does the job.

[sql]SELECT * FROM post WHERE id IN (SELECT post_id FROM favorite WHERE user_id=:user_id)[/sql]

However I want to avoid writing SQL and instead would prefer to use Yii AR functions to do the same. Could anyone please help me in substituting this SQL with appropriate Yii conventions.

There is no way you can use subquery using just AR functions (at least I do not know such way…). You will have to do something like this:

$posts = Post::model()->findAll( array( 

         'condition'=>'t.id IN (SELECT post_id FROM favorite WHERE user_id=:user_id)',

         'params'=>array( ':user_id'=>$userid )

) );

I do not think it is something wrong to use subqueries in this way. You may also move this condition to scope.

As I see it, you just create a new relation in Favorite:

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

And then get the data:

$favorites = Favorite::model()->with('post')->findAllByAttributes(array('user_id' => 1));


foreach ($favorites as $favorite) {

   echo $favorite->post->title;

}

@redguy and @wisp Thanks to you both for the help!