Fetch Data With Mutiple Models

I’m trying to fetch data from the database with an innerjoin and criteria. I want to have al the result of the users and the videos.

For now i’m using the createCommand:

public function getRelatedByUser($users_id){

$videos = Yii::app()->db->createCommand()

                ->select('*')


                ->from('videos')


                ->join('users', 'videos.users_id = users.id')


                ->where('videos.users_id = :userId AND Active=:active'


                , array(':userId' => $users_id, ':active' => TRUE))                        


                ->order('Date desc')


                ->limit(5)


                ->queryAll();





        return $videos;

}

But i want to use the command with the yii model like:

public function getRelatedByUser($users_id){

       $criteria=new CDbCriteria;  


       $criteria->select = '*';


       $criteria->alias = 'videos';


       $criteria->join = 'INNER JOIN users ON videos.users_id = users.id';


       $criteria->condition = 'users.id = :userID AND Active=:active';


       $criteria->params = array (


           ':userID' => $users_id,


           ':active' => TRUE,


       );                


       return Videos::model()->findAll($criteria);

}

But the problem is, when i use the code above, i get only the values of Videos, because i use the Videos::model()…

Now i was wondering of there’s away to use multiple models, something like:

return VideosAndUsers::model()->findAll($criteria); or return All::model()->findAll($criteria);

please some help

hi,

you have to write t.* and u.*

$videos = Yii::app()->db->createCommand()

->select('t.*,u.*')

->from('videos t')

->join('users u', 't.users_id = u.id')

->where('t.user_id= :userid AND t.active= :active',array(':userid'=>$users_id,':active'=>TRUE))

->order('Date desc')

limit(5)

->queryAll();

Did you set up the relations() method in your Videos and Users models? If so, this is how to get the last 5 videos with their user:

$videos = Videos::model()->with('user')->findAll(array(

  'condition'=>'user.Active = 1',

  'limit'=>5,

  'order'=>'t.Date desc',

));


foreach($videos as $video)

{

  echo $video->user->username;

}

If you have an “active” scope in your Users model you can even remove ‘condition’ and use ->with(‘user:active’) instead.

All of this is explained on Working with Databases: Relational Active Record.