今天在群里有朋友问我yii的relations的问题，结果贴出来的代码居然是类似这样的：
- function relations(){
- return array(
- 'xx'=>array(self::STAT,'aa','aa_id')
- );
- }
我当时直接了当的对他说，错了，怎么可能会有self::STAT呢？只有has_many,has_one,belongs_to,many_many，stat是错的。结果他回答说我OUT了，STAT是新加入的。。。
于是欣欣然跑到YII的官网查看了一下GUIDE，果然在 Relational Active Record，一节中发现了这个Statistical Query。
官方怎么说来着？第一句就让我大吃一惊:
Note: Statistical query has been supported since version 1.0.4.
奇怪，以前怎么没注意？
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 is MANY_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 in relations(). 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, itscommentCountwould receive this value. The default value for this option is 0. -
condition: theWHEREclause. 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 BYclause. It defaults to empty. -
group: theGROUP BYclause. It defaults to empty. -
having: theHAVINGclause. It defaults to empty.
关键的是看最后这几个条件，可以让你知道这些参数如何配置。。。
官方的GUIDE地址：http://www.yiiframework.com/doc/guide/1.1/en/database.arr