Manual JOIN trouble in a relation.

Hi all,

I’m trying to create a relation that will allow me to fetch the current “Versions.label” based on each widgets per project. The below relation generates the correct SQL statement, but I can not fetch the variable “Versions.label” because the “SELECT” portion of the generated query do not know about the joined table. Please help so that I can do model->label (where label corresponds to Versions.label).


  public function relations()

  {

    return array

    (

      'widget_versions'=>array(self::MANY_MANY, 'Widgets', 'ProjectWidgets(project_id, widget_id)',

                              'join'=>'LEFT OUTER JOIN `Versions` ON (`Versions`.`id`=widget_version_id)')

    );

  }

Thanks!

This is what Yii produces:


SELECT `t`.`id` AS `t0_c0`, `t`.`title` AS `t0_c1`, `t`.`url`

AS `t0_c2`, `t`.`svn_repository` AS `t0_c3`, `t`.`dt_create` AS `t0_c4`,

`widget_versions`.`id` AS `t1_c0`, `widget_versions`.`version_master_id` AS

`t1_c1`, `widget_versions`.`title` AS `t1_c2`,

`widget_versions`.`view_alias` AS `t1_c3`, `widget_versions`.`dt_create` AS

`t1_c4`, `widget_versions`.`id` AS `t1_c0`


FROM `Projects` `t`


LEFT OUTER JOIN `ProjectWidgets` `widget_versions_widget_versions` ON

(`t`.`id`=`widget_versions_widget_versions`.`project_id`) LEFT OUTER JOIN

`Widgets` `widget_versions` ON

(`widget_versions`.`id`=`widget_versions_widget_versions`.`widget_id`) LEFT

OUTER JOIN `Versions` ON (`Versions`.`id`=widget_version_id) WHERE

(`t`.`id`=7)

I just need it to look like this:


SELECT `t`.`id` AS `t0_c0`, `t`.`title` AS `t0_c1`, `t`.`url`

AS `t0_c2`, `t`.`svn_repository` AS `t0_c3`, `t`.`dt_create` AS `t0_c4`,

`widget_versions`.`id` AS `t1_c0`, `widget_versions`.`version_master_id` AS

`t1_c1`, `widget_versions`.`title` AS `t1_c2`,

`widget_versions`.`view_alias` AS `t1_c3`, `widget_versions`.`dt_create` AS

`t1_c4`, `widget_versions`.`id` AS `t1_c0`,


`Versions`.`label` AS `versions_label` FROM `Projects` `t`


LEFT OUTER JOIN `ProjectWidgets` `widget_versions_widget_versions` ON

(`t`.`id`=`widget_versions_widget_versions`.`project_id`) LEFT OUTER JOIN

`Widgets` `widget_versions` ON

(`widget_versions`.`id`=`widget_versions_widget_versions`.`widget_id`) LEFT

OUTER JOIN `Versions` ON (`Versions`.`id`=widget_version_id) WHERE

(`t`.`id`=7)

Ok, I got it to work. For those interested, follow the last post:

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

accessing relational data

by dreamynsx at 2009/10/21 11:29am