微生活

Yii 多表关联取数据 relations 写法

作者：Jeen 发布于：2013-5-7 17:10 Tuesday 分类：工作笔记

常用relations 写法参考：
如： A为用户表， B为用户标签表， C为标签信息表
数据表存储 结构
table A    ID    NAME
table B    ID    A_ID  TYPE_ID      
table C    ID    VALUE
其中A表中ID 与 B表中A_ID 关联 ( 1对n)
   B表中的TYPE_ID 与 C表中的ID 关联  (1对1)
需求示例： 查询并显示用户名为 ***  的用户的 所有标签详细信息
简要分析：查询条件 A.NAME = ***  ,最终希望获取到C表中的详细信息 ,  首先查询到用户ID，关联到B表中 查出此用户的 所有标签ID，通过标签ID获取到详细信息
ModelA
  'relab'=>array(self::HAS_MANY , 'ModelB' , array( 'A_ID' => 'ID' ) , 'with'=>'relbc' )    //模型A中查询B表中的数据

ModelB
  'relbc'=>array(self::BELONGS_TO , 'ModelC' , array( 'TYPE_ID'=> 'ID' ) )   //通过B模型 关联查询C表中的数据

$result = ModelA -> find(‘NAME=:NAME’,array(':NAME' => '***' )) -> with('relab');
 
最终调用：
由于是一对多的关系，所以   $result -> relab   一般是一个对象数组，这样我们就不能直接用  $result -> relab -> relbc 来调用C表中的字段信息了
 不过foreach 循环一下就ok了， 具体情况具体操作吧 :)

从B关联到C 为一一对应关系， 所以关联类型也可以使用 HAS_ONE,  不过在对应参数就需要作对应的小修改了
//HAS_ONE写法  'relbc'=>array(self::HAS_ONE , 'ModelC' , array( 'ID'=> 'TYPE_ID' ) )

//以下为framework/db/ar/CActiveRecord.php 中节选的部分注释，实际使用过程中可作参考
This method should be overridden to declare related objects.

There are four types of relations that may exist between two active record objects:
<ul>
<li>BELONGS_TO: e.g. a member belongs to a team;</li>
<li>HAS_ONE: e.g. a member has at most one profile;</li>
<li>HAS_MANY: e.g. a team has many members;</li>
<li>MANY_MANY: e.g. a member has many skills and a skill belongs to a member.</li>
</ul>

Besides the above relation types, a special relation called STAT is also supported
that can be used to perform statistical query (or aggregational query).
It retrieves the aggregational information about the related objects, such as the number
of comments for each post, the average rating for each product, etc.

Each kind of related objects is defined in this method as an array with the following elements:
<pre>
'varName'=>array('relationType', 'className', 'foreignKey', ...additional options)
</pre>
where 'varName' refers to the name of the variable/property that the related object(s) can
be accessed through; 'relationType' refers to the type of the relation, which can be one of the
following four constants: self::BELONGS_TO, self::HAS_ONE, self::HAS_MANY and self::MANY_MANY;
'className' refers to the name of the active record class that the related object(s) is of;
and 'foreignKey' states the foreign key that relates the two kinds of active record.
Note, for composite foreign keys, they can be either listed together, separated by commas or specified as an array
in format of array('key1','key2'). In case you need to specify custom PK->FK association you can define it as
array('fk'=>'pk'). For composite keys it will be array('fk_c1'=>'pk_c1','fk_c2'=>'pk_c2').
For foreign keys used in MANY_MANY relation, the joining table must be declared as well
(e.g. 'join_table(fk1, fk2)').

Additional options may be specified as name-value pairs in the rest array elements:
<ul>
<li>'select': string|array, a list of columns to be selected. Defaults to '', meaning all columns.
  Column names should be disambiguated if they appear in an expression (e.g. COUNT(relationName.name) AS name_count).</li>
<li>'condition': string, the WHERE clause. Defaults to empty. Note, column references need to
  be disambiguated with prefix 'relationName.' (e.g. relationName.age&gt;20)</li>
<li>'order': string, the ORDER BY clause. Defaults to empty. Note, column references need to
  be disambiguated with prefix 'relationName.' (e.g. relationName.age DESC)</li>
<li>'with': string|array, a list of child related objects that should be loaded together with this object.
  Note, this is only honored by lazy loading, not eager loading.</li>
<li>'joinType': type of join. Defaults to 'LEFT OUTER JOIN'.</li>
<li>'alias': the alias for the table associated with this relationship.
  It defaults to null,
  meaning the table alias is the same as the relation name.</li>
<li>'params': the parameters to be bound to the generated SQL statement.
  This should be given as an array of name-value pairs.</li>
<li>'on': the ON clause. The condition specified here will be appended
  to the joining condition using the AND operator.</li>
<li>'index': the name of the column whose values should be used as keys
  of the array that stores related objects. This option is only available to
  HAS_MANY and MANY_MANY relations.</li>
<li>'scopes': scopes to apply. In case of a single scope can be used like 'scopes'=>'scopeName',
  in case of multiple scopes can be used like 'scopes'=>array('scopeName1','scopeName2').
  This option has been available since version 1.1.9.</li>
</ul>

The following options are available for certain relations when lazy loading:
<ul>
<li>'group': string, the GROUP BY clause. Defaults to empty. Note, column references need to
  be disambiguated with prefix 'relationName.' (e.g. relationName.age). This option only applies to HAS_MANY and MANY_MANY relations.</li>
<li>'having': string, the HAVING clause. Defaults to empty. Note, column references need to
  be disambiguated with prefix 'relationName.' (e.g. relationName.age). This option only applies to HAS_MANY and MANY_MANY relations.</li>
<li>'limit': limit of the rows to be selected. This option does not apply to BELONGS_TO relation.</li>
<li>'offset': offset of the rows to be selected. This option does not apply to BELONGS_TO relation.</li>
<li>'through': name of the model's relation that will be used as a bridge when getting related data. Can be set only for HAS_ONE and HAS_MANY. This option has been available since version 1.1.7.</li>
</ul>

Below is an example declaring related objects for 'Post' active record class:
<pre>
return array(
    'author'=>array(self::BELONGS_TO, 'User', 'author_id'),
    'comments'=>array(self::HAS_MANY, 'Comment', 'post_id', 'with'=>'author', 'order'=>'create_time DESC'),
    'tags'=>array(self::MANY_MANY, 'Tag', 'post_tag(post_id, tag_id)', 'order'=>'name'),
);
</pre>

@return array list of related object declarations. Defaults to empty array.

标签: Yii