yiisoft/yii — pull request #1687 by resurtm (2012-11-11 09:14:02+00:00)
Title: [WIP] Fixes #1271

Work in progress and just the idea of solution. Fixes #1271.

I'm proposing to slightly expand `through` property micro-format and type (was string, now array). All details about testing environment can be found [here](https://github.com/yiisoft/yii/issues/1271#issuecomment-10264172). With this fix relations of the model `Kind1` will look like:

```php
return array(
'kind2s' => array(self::HAS_MANY, 'Kind2', 'kind1_id'),
'kind3s' => array(self::HAS_MANY, 'Kind3', array('id'=>'kind2_id'), 'through'=>array('kind2s'=>'kind2s_kind3s')),
'kind4s' => array(self::HAS_MANY, 'Kind4', array('id'=>'kind2_id'), 'through'=>array('kind2s'=>'kind2s_kind4s')),
);
```

Proposed format of the `through` property:
```php
'through'=>'junctionRelation', // old
'through'=>array('junctionRelation'=>'temporaryJunctionRelationAlias'), // new
```

Produced final SQL for **lazy loading**:
```sql
SELECT * FROM `tbl_kind1` `t` WHERE `t`.`id`=2 LIMIT 1;

SELECT `kind3s`.`id` AS `t1_c0`,
`kind3s`.`kind2_id` AS `t1_c1`,
`kind3s`.`title` AS `t1_c2`
FROM `tbl_kind3` `kind3s`
LEFT OUTER JOIN `tbl_kind2` `kind2s_kind3s`
ON ( `kind2s_kind3s`.`id` = `kind3s`.`kind2_id` )
WHERE ( `kind2s_kind3s`.`kind1_id` = :ypl0 );

SELECT `kind4s`.`id` AS `t1_c0`,
`kind4s`.`kind2_id` AS `t1_c1`,
`kind4s`.`title` AS `t1_c2`
FROM `tbl_kind4` `kind4s`
LEFT OUTER JOIN `tbl_kind2` `kind2s_kind4s`
ON ( `kind2s_kind4s`.`id` = `kind4s`.`kind2_id` )
WHERE ( `kind2s_kind4s`.`kind1_id` = :ypl0 );
```

Produced final SQL for **eager loading**:
```sql
SELECT `t`.`id` AS `t0_c0`,
`t`.`title` AS `t0_c1`,
`kind3s`.`id` AS `t1_c0`,
`kind3s`.`kind2_id` AS `t1_c1`,
`kind3s`.`title` AS `t1_c2`,
`kind4s`.`id` AS `t3_c0`,
`kind4s`.`kind2_id` AS `t3_c1`,
`kind4s`.`title` AS `t3_c2`
FROM `tbl_kind1` `t`
LEFT OUTER JOIN `tbl_kind2` `kind2s_kind3s`
ON ( `kind2s_kind3s`.`kind1_id` = `t`.`id` )
LEFT OUTER JOIN `tbl_kind3` `kind3s`
ON ( `kind2s_kind3s`.`id` = `kind3s`.`kind2_id` )
LEFT OUTER JOIN `tbl_kind2` `kind2s_kind4s`
ON ( `kind2s_kind4s`.`kind1_id` = `t`.`id` )
LEFT OUTER JOIN `tbl_kind4` `kind4s`
ON ( `kind2s_kind4s`.`id` = `kind4s`.`kind2_id` )
WHERE ( `t`.`id` = 2 );
```

This would give us feature of specifying junction relation alias and will solve #1271.