格式:'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)
需要弄清楚的几点:
VarName指什么? 详见下面例2。
RelationType。一共有4种分别为self::HAS_MANY, self::BELONGS_TO, self::MANY_MANY, self::HAS_ONE。
ClassName。即关联的另一个/model/类名.php。
ForeignKey。写了ForeignKey是用主键(ClassName.PK)关联Self.ForeignKey,为空两个表不是用主键关联需要on
附加条件
两个表不是用主键关联
'user' => array(self::BELONGS_TO, 'OaskUser', '' ,'on'=>'name=userName and user.type=1' , 'select'=>'TrueName', 'order'=>'sort asc'),
BELONGS_TO
(属于): 如果表 A 和 B 之间的关系是一对多,则 表 B 属于 表 A ;
HAS_MANY
(有多个): 如果表 A 和 B 之间的关系是一对多,则 A 有多个 B ;
HAS_ONE
(有一个): 这是 HAS_MANY 的一个特例,A 最多有一个 B ;
MANY_MANY
(多对多): 这个对应于数据库中的多对多关系。 由于多数 DBMS 不直接支持 多对多 关系,因此需要有一个关联表将多对多关系分割为一对多关系。在 AR术语中,我们可以解释 MANY_MANY 为 BELONGS_TO 和 HAS_MANY 的组合。
STAT :除了上述的关系类型,一个特殊的关系,称为STAT也支持 可用于进行统计查询(或聚合查询) 。 检索聚合对相关对象的信息,如数量。如 一篇日志共有多少个评论
'detailCounts' => array(self::STAT, 'PurchaseDetail', 'purchase_hid'),
(1)models/Post.php
class Post extends CActiveRecord { ...... public function relations() { return array( 'author'=>array(self::BELONGS_TO, 'User', 'author_id'), 'categories'=>array(self::MANY_MANY, 'Category', 'tbl_post_category(post_id, category_id)'), ); } }
其中Post与User的关系是BELONGS_TO(多对一)关系,并通过Post的author_id与User关联。Post中author_id是外键,关联到User中。注:此处的VarName是author,一个对象 。
(2)models/User.php
class User extends CActiveRecord { ...... public function relations() { return array( 'posts'=>array(self::HAS_MANY, 'Post', 'author_id'), ); } }
对于User,与Post的关系是属于HAS_MANY(一对多)关系。并通过Post的author_id与Post关联。 由于两者是一对多关系,所以要用posts
在FailParts.php中
'Users' => array(self::MANY_MANY, 'User', 'fail_parts_user(fail_parts_id, user_id)'),
在User.php中
'FailParts' => array(self::MANY_MANY, 'FailParts', 'fail_parts_user(user_id, fail_parts_id)'),
由于两者是多对多关系,所以要用Users ,而不是User;要用FailParts,而不是FailPart。此处的Users和FailParts,即为前面的VarName。
对于类A.php,'VarName'=>array('RelationType', 'B', 'ForeignKey', ...additional options)
其中VarName与B基本相同。但未必完全一样。此时就可以在A的views/A/xx.php中通过VarName来访问B及其属性值了。
如果是一对一/一对多:A->VarName->name
如果是多对多:
$users = As->VarName foreach($users as $u){ $_tmp_users[] = $u -> getName; } userStr = implode(', ', $_tmp_users);
渴求式加载(eager loading)方式。$posts=Post::model()->with('author','categories')->findAll();
AR scopes
//SchoolDepartment::model()->allDepartmentAndClass($id)->findAll(); public function relations() { return array( 'class' => array(self::HAS_MANY, 'SchoolClass', 'school_department_id'), ); } public function scopes() { return array( 'allDepartmentAndClass', ); } public function allDepartmentAndClass($id) { $this->getDbCriteria()->mergeWith(array( 'with' => "class", 'condition' => "t.tbl_school_id=$id", )); return $this; }
强制查询使用join连接查询
$criteria->together = true;
User类中的relations方法如下
public function relations() { return array( 'posts' => array(self::HAS_MANY, 'Post', 'author_id', 'order'=>'posts.update_time DESC', 'with'=>'comments:approved', // $user = User::model()->findByPk(1); 这里也查出了每篇post所带的comments //approved是comment的命名空间,可以在这里设置 //'together'=>false, 设置这一项,关联查新将被分为几个SQL语句执行,和性能有关系 ), 'postCount'=>array( self::STAT,'Post','author_id', 'condition'=>'status='.Post::STATUS_PUBLISHED, ), ); }
Post中的方法如下 :
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( 'author'=>array(self::BELONGS_TO,'User','author_id', //'select'=>'id,username,profile', // 关联查询的选项,如果不设置,则默认为*即整个关联记录 ), 'comments'=>array(self::HAS_MANY,'Comment','post_id', 'condition'=>'comments.status='.Comment::STATUS_APPROVED, 'order'=>'comments.create_time DESC'), 'commentCount'=>array( self::STAT,'Comment','post_id', 'condition'=>'status='.Comment::STATUS_APPROVED ), ); }
Comment中的ralations方法如下:
public function attributeLabels() //名字和现实标签的映射数组 { return array( 'id' => 'Id', 'content' => 'Comment', 'status' => 'Status', 'create_time' => 'Create Time', 'author' => 'Name', 'email' => 'Email', 'url' => 'Website', 'post_id' => 'PostID', //对应的博客ID ); }
在控制器中写个方法测试一下结果:
public function actionRQ(){ $post = Post::model()->find('id=:id',array(':id'=>7)); echo $post->author->username; echo "<hr>"; $posts = Post::model()->with('author','comments')->findAll(); //急切加载 foreach($posts as $post){ echo $post->id." |"; foreach($post->comments as $comment){ echo $comment->id.": "; echo $comment->content."<br>"; } echo $post->author->username."<br>"; } echo "<hr>"; $user = User::model()->with('posts.comments')->findByPk(1); //$user = User::model()->findByPk(1); 这一句和上一句是一样的,因为在User的relations声明的posts也已经加上了关联查询:with=>'comments' foreach($user->posts as $post){ //嵌套急切加载 echo $post->title." ("; foreach($post->comments as $comment){ echo $comment->id." : "; echo $comment->content."<br>"; } echo ")".$post->tags."<br>"; } echo "<hr>"; $criteria = new CDbCriteria; //$criteria->select = "username"; //$criteria->order //$criteria->limit //$criteria->condition //$criteria->params $criteria->with = array( 'posts.comments', ); $users = User::model()->findAll($criteria); foreach($users as $user){ echo $user->username.":<br>"; //echo $user->password; foreach($user->posts as $post){ //嵌套急切加载 echo $post->title." ("; foreach($post->comments as $comment){ echo $comment->id." : "; echo $comment->content."<br>"; } echo ")".$post->tags."<br>"; } } //动态关联查询,也就是在查询的时候覆盖relations中设置的关联的选项 echo "<hr>"; $user=User::model()->with(array( 'posts'=>array( 'order'=>'posts.update_time DESC', 'with'=>array('comments'=>array('order'=>'comments.id ASC')), //'together'=>false, //关联声明中设置together 选项为false 以便一些表被连接在单独的SQL语句中,分为几个SQL语句执行 ), ))->findByPk(1); echo "demo 的posts数量为:".$user->postCount; echo "<br>"; foreach($user->posts as $post){ echo $post->id."("; echo $post->title."的评论数量是:".$post->commentCount."<br>"; foreach($post->comments as $comment){ echo $comment->id." | "; } echo ")"; echo "<br>"; } }