Yii的学习(5)--Active Record的关联,yii--active_PHP教程
Yii的学习(5)--Active Record的关联,yii--active
官网原文:http://www.yiiframework.com/doc/guide/1.1/zh_cn/database.arr
官网中后半段为英文,而且中文的内容比英文少一些,先放到这里,之后有时间再翻译。
我们已经了解了怎样使用 Active Record (AR) 从单个数据表中获取数据。 在本节中,我们讲解怎样使用 AR 连接多个相关数据表并取回关联(join)后的数据集。
为了使用关系型 AR,我们建议在需要关联的表中定义主键-外键约束。这些约束可以帮助保证相关数据的一致性和完整性。
为简单起见,我们使用如下所示的实体-关系(ER)图中的数据结构演示此节中的例子。
信息: 对外键约束的支持在不同的 DBMS 中是不一样的。 SQLite
1. 声明关系
在我们使用 AR 执行关联查询之前,我们需要让 AR 知道一个 AR 类是怎样关联到另一个的。
两个 AR 类之间的关系直接通过 AR 类所代表的数据表之间的关系相关联。 从数据库的角度来说,表 A 和 B 之间有三种关系:一对多(one-to-many,例如 tbl_user
和 tbl_post
),一对一( one-to-one 例如 tbl_user
和tbl_profile
)和 多对多(many-to-many 例如 tbl_category
和 tbl_post
)。 在 AR 中,有四种关系:
-
BELONGS_TO
(属于): 如果表 A 和 B 之间的关系是一对多,则 表 B 属于 表 A (例如Post
属于User
); -
HAS_MANY
(有多个): 如果表 A 和 B 之间的关系是一对多,则 A 有多个 B (例如User
有多个Post
); -
HAS_ONE
(有一个): 这是HAS_MANY
的一个特例,A 最多有一个 B (例如User
最多有一个Profile
); -
MANY_MANY
: 这个对应于数据库中的 多对多 关系。 由于多数 DBMS 不直接支持 多对多 关系,因此需要有一个关联表将 多对多 关系分割为 一对多 关系。 在我们的示例数据结构中,tbl_post_category
就是用于此目的的。在 AR 术语中,我们可以解释MANY_MANY
为BELONGS_TO
和HAS_MANY
的组合。 例如,Post
属于多个(belongs to many)Category
,Category
有多个(has many)Post
.
AR 中定义关系需要覆盖 CActiveRecord 中的 relations() 方法。此方法返回一个关系配置数组。每个数组元素通过如下格式表示一个单一的关系。
'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)
其中 VarName
是关系的名字;RelationType
指定关系类型,可以是一下四个常量之一: self::BELONGS_TO
,self::HAS_ONE
, self::HAS_MANY
and self::MANY_MANY
;ClassName
是此 AR 类所关联的 AR 类的名字;ForeignKey
指定关系中使用的外键(一个或多个)。额外的选项可以在每个关系的最后指定(稍后详述)。
以下代码演示了怎样定义 User
和 Post
类的关系:
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)'), ); } } class User extends CActiveRecord { ...... public function relations() { return array( 'posts'=>array(self::HAS_MANY, 'Post', 'author_id'), 'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'), ); } }
信息: 外键可能是复合的,包含两个或更多个列。 这种情况下,我们应该将这些外键名字链接,中间用空格或逗号分割。对于 MANY_MANY
关系类型, 关联表的名字必须也必须在外键中指定。例如, Post
中的 categories
关系由外键 tbl_post_category(post_id, category_id)
指定。
AR 类中的关系定义为每个关系向类中隐式添加了一个属性。在一个关联查询执行后,相应的属性将将被以关联的 AR 实例填充。 例如,如果 $author
代表一个 User
AR 实例, 我们可以使用 $author->posts
访问其关联的Post
实例。
2. 执行关联查询
执行关联查询最简单的方法是读取一个 AR 实例中的关联属性。如果此属性以前没有被访问过,则一个关联查询将被初始化,它将两个表关联并使用当前 AR 实例的主键过滤。 查询结果将以所关联 AR 类的实例的方式保存到属性中。这就是传说中的 懒惰式加载(lazy loading,也可译为 迟加载) 方式,例如,关联查询只在关联的对象首次被访问时执行。 下面的例子演示了怎样使用这种方式:
// 获取 ID 为 10 的帖子 $post=Post::model()->findByPk(10); // 获取帖子的作者(author): 此处将执行一个关联查询。 $author=$post->author;
信息: 如果关系中没有相关的实例,则相应的属性将为 null 或一个空数组。 BELONGS_TO
和 HAS_ONE
关系的结果是 null, HAS_MANY
和 MANY_MANY
的结果是一个空数组。 注意, HAS_MANY
和MANY_MANY
关系返回对象数组,你需要在访问任何属性之前先遍历这些结果。 否则,你可能会收到 "Trying to get property of non-object(尝试访问非对象的属性)" 错误。
懒惰式加载用起来很方便,但在某些情况下并不高效。如果我们想获取 N
个帖子的作者,使用这种懒惰式加载将会导致执行 N
个关联查询。 这种情况下,我们应该改为使用 渴求式加载(eager loading)方式。
渴求式加载方式会在获取主 AR 实例的同时获取关联的 AR 实例。 这是通过在使用 AR 中的 find 或 findAll 方法时配合使用 with 方法完成的。例如:
$posts=Post::model()->with('author')->findAll();
上述代码将返回一个 Post
实例的数组。与懒惰式加载方式不同,在我们访问每个 Post
实例中的 author
属性之前,它就已经被关联的 User
实例填充了。 渴求式加载通过 一个 关联查询返回所有帖子及其作者,而不是对每个帖子执行一次关联查询。
我们可以在 with() 方法中指定多个关系名字,渴求式加载将一次性全部取回他们。例如,如下代码会将帖子连同其作者和分类一并取回。
$posts=Post::model()->with('author','categories')->findAll();
我们也可以实现嵌套的渴求式加载。像下面这样, 我们传递一个分等级的关系名表达式到 with() 方法,而不是一个关系名列表:
$posts=Post::model()->with( 'author.profile', 'author.posts', 'categories')->findAll();
上述示例将取回所有帖子及其作者和所属分类。它还同时取回每个作者的简介(author.profile)和帖子(author.posts)。
从版本 1.1.0 开始,渴求式加载也可以通过指定 CDbCriteria::with 的属性执行,就像下面这样:
$criteria=new CDbCriteria; $criteria->with=array( 'author.profile', 'author.posts', 'categories', ); $posts=Post::model()->findAll($criteria);
或者
$posts=Post::model()->findAll(array( 'with'=>array( 'author.profile', 'author.posts', 'categories', ) );
3. 关系型查询选项
我们提到在关系声明时可以指定附加的选项。这些 名-值 对形式的选项用于自定义关系型查询。概括如下:
-
select
: 关联的 AR 类中要选择(select)的列的列表。 默认为 '*',即选择所有列。此选项中的列名应该是已经消除歧义的。 -
condition
: 即WHERE
条件。默认为空。此选项中的列名应该是已经消除歧义的。 -
params
: 要绑定到所生成的 SQL 语句的参数。应该以 名-值 对数组的形式赋值。此选项从 1.0.3 版起有效。 -
on
: 即ON
语句。此处指定的条件将会通过AND
操作符附加到 join 条件中。此选项中的列名应该是已经消除歧义的。 此选项不会应用到MANY_MANY
关系中。此选项从 1.0.2 版起有效。 -
order
: 即ORDER BY
语句。默认为空。 此选项中的列名应该是已经消除歧义的。 -
with
: a list of child related objects that should be loaded together with this object. Be aware that using this option inappropriately may form an infinite relation loop. -
joinType
: type of join for this relationship. It defaults toLEFT OUTER JOIN
. -
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. -
together
: whether the table associated with this relationship should be forced to join together with the primary table and other tables. This option is only meaningful forHAS_MANY
andMANY_MANY
relations. If this option is set false, the table associated with theHAS_MANY
orMANY_MANY
relation will be joined with the primary table in a separate SQL query, which may improve the overall query performance since less duplicated data is returned. If this option is set true, the associated table will always be joined with the primary table in a single SQL query, even if the primary table is paginated. If this option is not set, the associated table will be joined with the primary table in a single SQL query only when the primary table is not paginated. For more details, see the section "Relational Query Performance". -
join
: the extraJOIN
clause. It defaults to empty. This option has been available since version 1.1.3. -
group
: theGROUP BY
clause. It defaults to empty. Column names referenced in this option should be disambiguated. -
having
: theHAVING
clause. It defaults to empty. Column names referenced in this option should be disambiguated. -
index
: the name of the column whose values should be used as keys of the array that stores related objects. Without setting this option, an related object array would use zero-based integer index. This option can only be set forHAS_MANY
andMANY_MANY
relations. -
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.
In addition, the following options are available for certain relationships during lazy loading:
-
limit
: limit of the rows to be selected. This option does NOT apply toBELONGS_TO
relation. -
offset
: offset of the rows to be selected. This option does NOT apply toBELONGS_TO
relation. -
through
: name of the model's relation that will be used as a bridge when getting related data. This option has been available since version 1.1.7 where it can be used forHAS_ONE
andHAS_MANY
. Since 1.1.14 it can be used forBELONGS_TO
as well.
Below we modify the posts
relationship declaration in the User
by including some of the above options:
class User extends CActiveRecord { public function relations() { return array( 'posts'=>array(self::HAS_MANY, 'Post', 'author_id', 'order'=>'posts.create_time DESC', 'with'=>'categories'), 'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'), ); } }
Now if we access $author->posts
, we would obtain the author's posts sorted according to their creation time in descending order. Each post instance also has its categories loaded.
5. Disambiguating Column Names
When a column name appears in two or more tables being joined together, it needs to be disambiguated. This is done by prefixing the column name with its table's alias name.
In relational AR query, the alias name for the primary table is fixed as t
, while the alias name for a relational table is the same as the corresponding relation name by default. For example, in the following statement, the alias name for Post
and Comment
is t
and comments
, respectively:
$posts=Post::model()->with('comments')->findAll();
Now assume both Post
and Comment
have a column called create_time
indicating the creation time of a post or comment, and we would like to fetch posts together with their comments by ordering first the posts' creation time and then the comments' creation time. We need to disambiguate the create_time
column like the following:
$posts=Post::model()->with('comments')->findAll(array( 'order'=>'t.create_time, comments.create_time' ));
Tip: The default alias of a related table is the name of the relation. Please note that if you're using relation from within another relation the alias will be the former relation name only and will not be prefixed with the parent relation. For example, the alias for 'author.group' relation is 'group', not 'author.group'.
$posts=Post::model()->with('author', 'author.group')->findAll(array( 'order'=>'group.name, author.name, t.title' ));
You can avoid the collision of tables' aliases by specifying the alias property of the relation.
$comments=Comment::model()->with( 'author', 'post', 'post.author'=>array('alias'=>'p_author'))->findAll(array( 'order'=>'author.name, p_author.name, post.title' ));
6. Dynamic Relational Query Options
We can use dynamic relational query options in both with() and the with
option. The dynamic options will overwrite existing options as specified in the relations() method. For example, with the above User
model, if we want to use eager loading approach to bring back posts belonging to an author in ascending order (theorder
option in the relation specification is descending order), we can do the following:
User::model()->with(array( 'posts'=>array('order'=>'posts.create_time ASC'), 'profile', ))->findAll();
Dynamic query options can also be used when using the lazy loading approach to perform relational query. To do so, we should call a method whose name is the same as the relation name and pass the dynamic query options as the method parameter. For example, the following code returns a user's posts whose status
is 1:
$user=User::model()->findByPk(1); $posts=$user->posts(array('condition'=>'status=1'));
7. Relational Query Performance
As we described above, the eager loading approach is mainly used in the scenario when we need to access many related objects. It generates a big complex SQL statement by joining all needed tables. A big SQL statement is preferrable in many cases since it simplifies filtering based on a column in a related table. It may not be efficient in some cases, however.
Consider an example where we need to find the latest posts together with their comments. Assuming each post has 10 comments, using a single big SQL statement, we will bring back a lot of redundant post data since each post will be repeated for every comment it has. Now let's try another approach: we first query for the latest posts, and then query for their comments. In this new approach, we need to execute two SQL statements. The benefit is that there is no redundancy in the query results.
So which approach is more efficient? There is no absolute answer. Executing a single big SQL statement may be more efficient because it causes less overhead in DBMS for parsing and executing the SQL statements. On the other hand, using the single SQL statement, we end up with more redundant data and thus need more time to read and process them.
For this reason, Yii provides the together
query option so that we choose between the two approaches as needed. By default, Yii uses eager loading, i.e., generating a single SQL statement, except when LIMIT
is applied to the primary model. We can set the together
option in the relation declarations to be true to force a single SQL statement even when LIMIT
is used. Setting it to false will result in some of tables will be joined in separate SQL statements. For example, in order to use separate SQL statements to query for the latest posts with their comments, we can declare the comments
relation in Post
class as follows,
public function relations() { return array( 'comments' => array(self::HAS_MANY, 'Comment', 'post_id', 'together'=>false), ); }
We can also dynamically set this option when we perform the eager loading:
$posts = Post::model()->with( array('comments'=>array( 'together'=>false )) )->findAll();
8. Statistical Query
Besides the relational query described above, Yii also supports the so-called statistical query (or aggregational query). It refers to retrieving the aggregational information about the related objects, such as the number of comments for each post, the average rating for each product, etc. Statistical query can only be performed for objects related in HAS_MANY
(e.g. a post has many comments) or MANY_MANY
(e.g. a post belongs to many categories and a category has many posts).
Performing statistical query is very similar to performing relation query as we described before. We first need to declare the statistical query in the relations() method of CActiveRecord like we do with relational query.
class Post extends CActiveRecord { public function relations() { return array( 'commentCount'=>array(self::STAT, 'Comment', 'post_id'), 'categoryCount'=>array( self::STAT, 'Category', 'post_category(post_id, category_id)' ), ); } }
In the above, we declare two statistical queries: commentCount
calculates the number of comments belonging to a post, and categoryCount
calculates the number of categories that a post belongs to. Note that the relationship between Post
and Comment
is HAS_MANY
, while the relationship between Post
and Category
isMANY_MANY
(with the joining table post_category
). As we can see, the declaration is very similar to those relations we described in earlier subsections. The only difference is that the relation type is STAT
here.
With the above declaration, we can retrieve the number of comments for a post using the expression $post->commentCount
. When we access this property for the first time, a SQL statement will be executed implicitly to retrieve the corresponding result. As we already know, this is the so-called lazy loading approach. We can also use the eager loading approach if we need to determine the comment count for multiple posts:
$posts=Post::model()->with('commentCount', 'categoryCount')->findAll();
The above statement will execute three SQLs to bring back all posts together with their comment counts and category counts. Using the lazy loading approach, we would end up with 2*N+1
SQL queries if there are N
posts.
By default, a statistical query will calculate the COUNT
expression (and thus the comment count and category count in the above example). We can customize it by specifying additional options when we declare it inrelations(). The available options are summarized as below.
-
select
: the statistical expression. Defaults toCOUNT(*)
, meaning the count of child objects. -
defaultValue
: the value to be assigned to those records that do not receive a statistical query result. For example, if a post does not have any comments, itscommentCount
would receive this value. The default value for this option is 0. -
condition
: theWHERE
clause. It defaults to empty. -
params
: the parameters to be bound to the generated SQL statement. This should be given as an array of name-value pairs. -
order
: theORDER BY
clause. It defaults to empty. -
group
: theGROUP BY
clause. It defaults to empty. -
having
: theHAVING
clause. It defaults to empty.
9. Relational Query with Named Scopes
Relational query can also be performed in combination with named scopes. It comes in two forms. In the first form, named scopes are applied to the main model. In the second form, named scopes are applied to the related models.
The following code shows how to apply named scopes to the main model.
$posts=Post::model()->published()->recently()->with('comments')->findAll();
This is very similar to non-relational queries. The only difference is that we have the with()
call after the named-scope chain. This query would bring back recently published posts together with their comments.
And the following code shows how to apply named scopes to the related models.
$posts=Post::model()->with('comments:recently:approved')->findAll(); // or since 1.1.7 $posts=Post::model()->with(array( 'comments'=>array( 'scopes'=>array('recently','approved') ), ))->findAll(); // or since 1.1.7 $posts=Post::model()->findAll(array( 'with'=>array( 'comments'=>array( 'scopes'=>array('recently','approved') ), ), ));
The above query will bring back all posts together with their approved comments. Note that comments
refers to the relation name, while recently
and approved
refer to two named scopes declared in the Comment
model class. The relation name and the named scopes should be separated by colons.
Occasionally you may need to retrieve a scoped relationship using a lazy-loading approach, instead of the normal eager loading method shown above. In that case, the following syntax will do what you need:
~~ [php] // note the repetition of the relationship name, which is necessary $approvedComments = $post->comments('comments:approved'); ~~
Named scopes can also be specified in the with
option of the relational rules declared inCActiveRecord::relations(). In the following example, if we access $user->posts
, it would bring back allapproved comments of the posts.
class User extends CActiveRecord { public function relations() { return array( 'posts'=>array(self::HAS_MANY, 'Post', 'author_id', 'with'=>'comments:approved'), ); } } // or since 1.1.7 class User extends CActiveRecord { public function relations() { return array( 'posts'=>array(self::HAS_MANY, 'Post', 'author_id', 'with'=>array( 'comments'=>array( 'scopes'=>'approved' ), ), ), ); } }
Note: Before 1.1.7 named scopes applied to related models must be specified in CActiveRecord::scopes. As a result, they cannot be parameterized.
Since 1.1.7 it's possible to pass parameters for relational named scopes. For example, if you have scope named rated
in the Post
that accepts minimum rating of post, you can use it from User
the following way:
$users=User::model()->findAll(array( 'with'=>array( 'posts'=>array( 'scopes'=>array( 'rated'=>5, ), ), ), )); class Post extends CActiveRecord { ...... public function rated($rating) { $this->getDbCriteria()->mergeWith(array( 'condition'=>'rating=:rating', 'params'=>array(':rating'=>$rating), )); return $this; } ...... }
10. Relational Query with through
When using through
, relation definition should look like the following:
'comments'=>array(self::HAS_MANY,'Comment',array('key1'=>'key2'),'through'=>'posts'),
In the above array('key1'=>'key2')
:
-
key1
is a key defined in relation specified inthrough
(posts
is this case). -
key2
is a key defined in a model relation points to (Comment
in this case).
through
can be used with HAS_ONE
, BELONGS_TO
and HAS_MANY
relations.
HAS_MANY
through
HAS_MANY through ER
An example of HAS_MANY
with through
is getting users from a particular group when users are assigned to groups via roles.
A bit more complex example is getting all comments for all users of a particular group. In this case we have to use several relations with through
in a single model:
class Group extends CActiveRecord { ... public function relations() { return array( 'roles'=>array(self::HAS_MANY,'Role','group_id'), 'users'=>array( self::HAS_MANY,'User',array('user_id'=>'id'),'through'=>'roles' ), 'comments'=>array( self::HAS_MANY,'Comment',array('id'=>'user_id'),'through'=>'users' ), ); } }
Usage examples
// get all groups with all corresponding users $groups=Group::model()->with('users')->findAll(); // get all groups with all corresponding users and roles $groups=Group::model()->with('roles','users')->findAll(); // get all users and roles where group ID is 1 $group=Group::model()->findByPk(1); $users=$group->users; $roles=$group->roles; // get all comments where group ID is 1 $group=Group::model()->findByPk(1); $comments=$group->comments;
HAS_ONE
through
HAS_ONE through ER
An example of using HAS_ONE
with through
is getting user address where user is bound to address using profile. All these entities (user, profile, and address) do have corresponding models:
class User extends CActiveRecord { ... public function relations() { return array( 'profile'=>array(self::HAS_ONE,'Profile','user_id'), 'address'=>array( self::HAS_ONE,'Address',array('id'=>'profile_id'), 'through'=>'profile' ), ); } }
Usage examples
// get address of a user whose ID is 1 $user=User::model()->findByPk(1); $address=$user->address;
through on self
through
can be used for a model bound to itself using a bridge model. In our case it's a user mentoring other users:
through self ER
That's how we can define relations for this case:
class User extends CActiveRecord { ... public function relations() { return array( 'mentorships'=>array( self::HAS_MANY,'Mentorship','teacher_id','joinType'=>'INNER JOIN' ), 'students'=>array( self::HAS_MANY,'User',array('student_id'=>'id'), 'through'=>'mentorships','joinType'=>'INNER JOIN' ), ); } }
Usage examples
// get all students taught by teacher whose ID is 1 $teacher=User::model()->findByPk(1); $students=$teacher->students;
1. Yii
Yii是一个基于组件的高性能的PHP的框架,用于开发大规模Web应用。Yii采用严格的OOP编写,并有着完善的库引用以及全面的教程。从MVC,DAO/ActiveRecord,widgets,caching,等级式RBAC,Web服务,到主体化,I18N和L10N,Yii提供了今日Web 2.0应用开发所需要的几乎一切功能。而且这个框架的价格也并不太高。事实上,Yii是最有效率的PHP框架之一。
2. CodeIgniter
CodeIgniter是一个应用开发框架——一个为建立PHP网站的人们所设计的工具包。其目标在于快速的开发项目:它提供了丰富的库组以完成常见的任务,以及简单的界面,富有条理性的架构来访问这些库。使用CodeIgniter开发可以往项目中注入更多的创造力,因为它节省了大量编码的时间。
3. CakePHP
CakePHP是一个快速开发PHP的框架,其中使用了一些常见的设计模式如ActiveRecord,Association Data Mapping,Front Controller以及MVC。其主要目标在于提供一个令任意水平的PHP开发人员都能够快速开发web应用的框架,而且这个快速的实现并没有牺牲项目的弹性。
4. PHPDevShell
PHPDevShell是一个开源(GNU/LGPL)的快速应用开发框架,用于开发不含Javascript的纯PHP。它有一个完整的GUI管理员后台界面。其主要目标在于开发插件一类的基于管理的应用,其中速度、安全、稳定性及弹性是最优先考虑的重点。其设计形成了一个简单的学习曲线,PHP开发者无需学习复杂的新术语。PHPDevShell的到来满足了开发者们对于一个轻量级但是功能完善,可以无限制的进行配置的GUI的需求。
5. Akelos
Akelos PHP框架是一个基于MVC设计模式的web应用开发平台。基于良好的使用习惯,使用它可以完成如下任务:
◆方便的使用Ajax编写views
◆通过控制器管理请求(request)及响应(response)
◆管理国际化的应用
◆使用简单的协议与模型及数据库通信
你的Akelos应用可以在大多数共享主机服务供应方上运行,因为Akelos对服务器唯一的要求就是支持PHP。因此,Akelos PHP框架是理想的用于发布单独web应用的框架,因为它不需要非标准PHP配置便能运行。
6. Symfony
Symfony是一个用于开发PHP5项目的web应用框架。
这个框架的目的在于加速web应用的开发以及维护,减少重复的编码工作。
Symfony的系统需求不高,可以被轻易的安装在任意设置上:你只需一个Unix或Windows,搭配一个安装了PHP5的网络服务器即可。它与差不多所有的数据库兼容。Symfony的价位不高,相比主机上的花销要低得多。
对于PHP开发者而言,使用Symfony是一件很自然的事,其学习曲线只有短短一天。干净的设计以及代码可读性将缩短开发时间。开发者可以将敏捷开发的原理(如DRY,KISS或XP等)应用在其中,将重点放在应用逻辑层面上,而不用花费大量时间在编写没完没了的XML配置文件上。
Symfony旨在建立企业级的完善应用程序。也就是说,你拥有整个设置的控制权:从路径结构到外部库,几乎一切都可以自定义。为了符合企业的开发条例,Symfony还绑定了一些额外的工具,以便于项目的测试,调试以及归档。
7. Prado
PRADO团队由一些PRADO*者......余下全文>>
给你几个关键字吧
1.MVC
2.事件驱动
3.ActiveRecord
4.面向组件
碰到这些不要放过,要仔细看