Yii: How to Fetch data from mutiple models with criteria?

Question

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);

Answer 1

In your Videos model create/edit the method

public function relations() {
    return array(
        'user' => array(self::BELONGS_TO, 'Users', 'users_id')
    );
}

(btw the correct way to name the column would be user_id and not users_id)

Then you can get your videos by

Videos::model()->with('user')->findAllByAttributes(
    array('users_id'=>$users_id, 'active'=>true),
    array('order'=>'`date` DESC')
);

This is pretty big bunch of code. We can do it even better. Just create/edit your model method

public function scopes() {
    return array(
        'sort_by_date_desc'=>array(
            'order'=>'`date` DESC'
        ),
    );
}

public function by_user_id($user_id) {
    $this->getDbCriteria()->addColumnCondition(
        array('users_id'=>$user_id)
    );
    return $this;
}

public function active_only() {
    $this->getDbCriteria()->addColumnCondition(
        array('active'=>true)
    );
    return $this;
}

Now we can do magic

$videos = Videos::model()->with('user')
    ->by_user_id($users_id)->sort_by_date_desc()
    ->active_only()->findAll();

Then you can do something like

foreach ($videos as $video)
    echo $video->title . ' by ' . $video->user->name;

As you can see, we access user profile for each video simply by calling relation name. There you have full model of user associated to video.