Yii - Joining multiple tables using active record

Question

I am currently working on a YII application, which I must admit is my first attempt. I am having difficulty mastering the active record component of YII. In particular I would like to join three tables using with().

My mysql tables are as follows:

video_specific
- id (primary key)
- random_id
- user_id
- video_link
- quality (enum, high,low)

video_details
- video_id
- upvote_count
- downvote_count
- timestamp

ladder_videos
- ladder_id
- video_id

ladder_specific
- id
- random_id
- name
- description
- ladder_type
- status
- video_count

Thus after using the gii tool I was give models with the following relationships. Please note that I did not create a model for the ladder_videos table.

In videoSpecific model

'ladderSpecifics' => array(self::MANY_MANY, 'LadderSpecific', 
'ladder_videos(video_id, ladder_id)'),
'videoDetails' => array(self::HAS_ONE, 'VideoDetails', 'video_id')

In ladderSpecific model

'videoSpecifics' => array(self::MANY_MANY, 'VideoSpecific', 
'ladder_videos(ladder_id, video_id)')

With these relations I thought I could right the following query

$ladders = LadderSpecific::model()->with(
        array('videoSpecifics'=>array('select'=>'id,video_link,random_id',
        'join'=>'videoDetails')))->findAll();

But I get the following error

    CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or
access violation: 1064 You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near 'videoDetails' 
at line 1. The SQL statement executed was: SELECT `t`.`id` AS `t0_c0`, `t`.`random_id` AS 
`t0_c1`, `t`.`name` AS `t0_c2`, `t`.`description` AS `t0_c3`, `t`.`ladder_type` AS 
`t0_c4`, `t`.`status` AS `t0_c5`, `t`.`video_count` AS `t0_c6`, `videoSpecifics`.`id` AS 
`t1_c0`, `videoSpecifics`.`video_link` AS `t1_c3`, `videoSpecifics`.`random_id` AS `t1_c1` 
FROM `ladder_specific` `t` LEFT OUTER JOIN `ladder_videos` `videoSpecifics_videoSpecifics` 
ON (`t`.`id`=`videoSpecifics_videoSpecifics`.`ladder_id`) LEFT OUTER JOIN `video_specific` 
`videoSpecifics` ON (`videoSpecifics`.`id`=`videoSpecifics_videoSpecifics`.`video_id`) 
videoDetails 

Any ideas why?? Please limit your answers to active record, and not DAO or query builder. Thanks

Answer 1

SO the answer was to update the ladderSpecific relations. So once you update them they should like this:

public function relations(){
        // NOTE: you may need to adjust the relation name and the related
        // class name for the relations automatically generated below.
        return array(
            'ladderDetails' => array(self::HAS_ONE, 'LadderDetails', 'ladder_id'),
            'videoSpecifics' => array(self::MANY_MANY, 'VideoSpecific', 'ladder_videos(ladder_id, video_id)'),
                                array(self::HAS_ONE, 'VideoDetails', 'video_id'),
        );
    }

Once you have updated this relation you can do this:

$ladders = LadderSpecific::model()->with(
                                        array('videoSpecifics.videoDetails'=>
                                            array(
                                                'order'=>'videoDetails.upvote_count - videoDetails.downvote_count DESC',
                                                'limit'=>'1'
                                            )))->findAll(array('order'=>'name',));

The only issue is for some reason I can not limit the number of records returned. Once I figure that out I will update my answer. If you have a better way of doing it let me know.

Answer 2

It would help to review how relations work in Yii.

http://www.yiiframework.com/doc/guide/1.1/en/database.arr

When querying the model, Yii automatically generates the query to fetch the relational data, so you don't have to supply it.

You must therefore supply the name given to your relation.

$ladders = LadderSpecific::model()->with('videoSpecifics')->findAll();