Yii - 使用活动记录连接多个表

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

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