yii模型中关系(relations)中非主键关联的解决方案

显示A表的记录，该表与B表如果通过主键关联，一对一的关系如下：

/** * @return array relational rules. */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('myauthitem'=>array(self::HAS_ONE,'AuthItem','userid'),);}

其中userid是AuthItem模型所对应表中的关联字段。YII默认是用主表的主键与该字段(userid)关联。可打开CWebLogRoute看一下，摘要如下：

Querying SQL: SELECT `t`.`id` AS `t0_c0`, `t`.`username` AS `t0_c1`,`t`.`password` AS `t0_c2`, `t`.`role` AS `t0_c3`, `t`.`description` AS`t0_c4`, `t`.`last_login_time` AS `t0_c5`, `t`.`last_login_ip` AS `t0_c6`,`myauthitem`.`name` AS `t1_c0`, `myauthitem`.`type` AS `t1_c1`,`myauthitem`.`description` AS `t1_c2`, `myauthitem`.`bizrule` AS `t1_c3`,`myauthitem`.`data` AS `t1_c4` FROM `kj_manager` `t`  LEFT OUTER JOIN`kj_authitem` `myauthitem` ON (`myauthitem`.`userid`=`t`.`id`)  LIMIT 10

但是，如果A表的非主键如何解决这个问题。

比如管理员表，字段为id,name,password,role,time....，role字段为“角色”属性，关联Authitem表的name字段，显示description信息。

可通过以下方案解决：

/** * @return array relational rules. */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('myauthitem'=>array(self::HAS_ONE,'AuthItem','','on'=>'t.role=myauthitem.name'),);}

‘on’: the ON clause. The condition specified here will be appended to the joining condition using the AND operator.

on子句，通过 and 操作符附加到join条件之后。

因此，relations关系中，如果没有主键关联，则第三个参数必须设置为空。

查看一下生成的SQL：

Querying SQL: SELECT `t`.`id` AS `t0_c0`, `t`.`username` AS `t0_c1`,`t`.`password` AS `t0_c2`, `t`.`role` AS `t0_c3`, `t`.`description` AS`t0_c4`, `t`.`last_login_time` AS `t0_c5`, `t`.`last_login_ip` AS `t0_c6`,`myauthitem`.`name` AS `t1_c0`, `myauthitem`.`type` AS `t1_c1`,`myauthitem`.`description` AS `t1_c2`, `myauthitem`.`bizrule` AS `t1_c3`,`myauthitem`.`data` AS `t1_c4` FROM `kj_manager` `t`  LEFT OUTER JOIN`kj_authitem` `myauthitem` ON (t.role=myauthitem.name)  LIMIT 10