Yii - CActiveDataProvider using relations

Question

I am trying to use relations as part of the criteria to create a CActiveDataProvider. I have the following:

User model:

return array(
        'favourites'=>array(self::HAS_MANY, 'UserFavourite', 'user_id', 'order'=>'added_at DESC'),
);

---

UserFavourite model:

return array(
        'user'=>array(self::BELONGS_TO, 'User', 'user_id'),
        'listing'=>array(self::BELONGS_TO, 'Listing', 'listing_id'),
);

---

Controller (view favourites action):

$user = $this->loadUser(Yii::app()->user->id);

---

How do I create a CActiveDataProvider in my User model that fetches all favourites by the current user - along with the related listing model data for each favourite? So I want to do something like $favourites = $user->getFavourites();.

The CActiveDataProvider should return an array of Listing objects. It should support pagination and sorting - the default sort will be added_at DESC.

I have tried using the with option of CDbCriteria but that does not seem to work...

Answer 1

You have not provided your database schema, but i guess this code could work,

$criteria=new CDbCriteria();
$criteria->with=array('favourites'=>array('with'=>'listing'));
$dataProvider= new CActiveDataProvider('User', array(
        'criteria'=>$criteria,
        'sort'=>array(
            'defaultOrder'=>'t.added_at DESC',
        )));

Please make sure your Relations are correct and according to schema.

• Infact, If you dont care about number of queries, you can always call something like

  $user->favourites;

It will return proper data for correct relations, Again you can do this

In order to, pass listing model to view go like this

foreach($user->favourites as $fav){

  $this->renderPartial("PATH TO YOUR VIEW",array('data'=>$fav->listing));

}

Note that there may be many Favourites for an user so you need to use foreach , unless you are not using CListview. Another way is to user Clistview in that case you may need to change your query.Let me know how that works for you.

Answer 2

In your Listing relations method you need to have the relations to Users through the intermediate table UsersFavourite. The relationship type is HAS_MANY:

return array(
         'favourites'=>array(self::HAS_MANY, 'UserFavourite', 'listing_id'),
         'users'=>array(self::HAS_MANY, 'User', array('user_id'=>'id'), 'through'=>'favourites')
);

In your User method that returns the CActiveDataProvider: The main model is Listing, joined to UserFavourite and User. You must set together to true to build one single sql query. Finally you must set the users.id to the current id of User model to bring only the rows relevant to the current user.

$criteria = new CDbCriteria;
$criteria->with = array( 'favourites','users =>'array('condition'=>"users.id=:user_id"));
$criteria->together = true;
$criteria->params = array(':user_id'=>$this->id);

return new CActiveDataProvider('Listing', array(
        'criteria'=>$criteria,
        'sort'=>array('defaultOrder'=>'favourites.added_at DESC')
));

I tested this answer and worked correctly with a small dataset.

Answer 3

Here is how I have achieved the required output:

$criteria = new CDbCriteria;
$criteria->condition = 'uf.user_id = :user_id';
$criteria->params = array(':user_id'=>$this->id);
$criteria->join = 'LEFT OUTER JOIN user_favourite uf ON uf.listing_id = t.id';
$criteria->order = 'uf.added_at DESC';

return new CActiveDataProvider('Listing', array(
    'criteria'=>$criteria,
));

In the end I have just hard-coded in the JOIN, rather than try and use the relation, as it seems it is not meant to work that way.