Yii - CActiveDataProvider using relationships
I am trying to use relationships as part of the criteria to create 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 favorite action):
$user = $this->loadUser(Yii::app()->user->id);
How do I create CActiveDataProvider
in my user model that fetches everything by the favourites
current user, along with the corresponding model data listing
for each select? So I want to do something like $favourites = $user->getFavourites();
.
CActiveDataProvider
should return an array of objects listing
. It must support pagination and sorting - sorting will be by default added_at DESC
.
I tried using the option with
CDbCriteria
, but it doesn't work ...
source to share
This is how I achieved the desired result:
$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,
));
At the end of the day, I'm just coded into a JOIN and not trying to use a relationship as it doesn't seem to be designed to work that way.
source to share
You have not specified your database schema, but I think this code might 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',
)));
Make sure your Relationship is correct and in line with the diagram.
-
Infact, If you don't care about the number of requests, you can always call something like
$ user-> favorites;
It will return the correct data for the correct relationship. Again you can do this
In order to skip the list model to view, do it like
foreach($user->favourites as $fav){
$this->renderPartial("PATH TO YOUR VIEW",array('data'=>$fav->listing));
}
Note that there may be many favorites for a user, so you need to use foreach if you are not using CListview . Another way is the Clistview user , in which case you may need to change your request. Let me know how it works for you.
source to share
In your method, Listing
relations
you need to refer to
Users
via an 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 method User
that returns CActiveDataProvider
: Base Model
Listing
, concatenated with UserFavourite
and User
. You have to set together
up true
to create one sql query. Finally, you must set users.id to the current model ID User
to only display rows related 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 it worked correctly with a small dataset.
source to share