Detailed explanation of relations data correlation query and statistical function usage in Yii

The examples in this article describe the usage of relations data correlation query and statistical functions in Yii. Share it with everyone for your reference, the details are as follows:

Related query, Yii also supports so-called statistical query (or aggregate query). It refers to retrieving aggregated information about associated objects, such as the number of comments for each post, the average rating of each product, etc. Statistical queries are only performed on objects associated with HAS_MANY (for example, a post has many comments) or MANY_MANY (for example, a post belongs to many categories and a category has many posts).

Performing statistical queries is very similar to the correlation query described previously. We first need to declare the statistical query in the relations() method of CActiveRecord.

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)'),
    );
  }
}

Related query namespace

Related queries can also be executed with namespaces. There are two forms. In the first form, the namespace is applied to the main model. In the second form, the namespace is applied to the associated model.

The following code shows how to apply the namespace to the main model.

$posts=Post::model()->published()->recently()->with('comments')->findAll();

This is very similar to a non-correlated query. The only difference is that we use the with() call after the namespace. This query should return recently published posts and their comments.

The following code shows how to apply a namespace to an associated model.

$posts=Post::model()->with('comments:recently:approved')->findAll();

The above query will return all posts and their moderated comments. Note that comments refers to the association name, while recently and approved refer to the namespace declared in the Comment model class. Association names and namespaces should be separated by colons.

Namespaces can also be specified in the with option of association rules declared in CActiveRecord::relations(). In the example below, if we access $user->posts, it will return all moderated comments for this post.

class User extends CActiveRecord
{
  public function relations()
  {
    return array(
      'posts'=>array(self::HAS_MANY, 'Post', 'author_id', 'with'=>'comments:approved'),
    );
  }
}

I hope this article will be helpful to everyone’s PHP program design based on the Yii framework.

For more detailed explanations on the usage of relations data correlation query and statistical functions in Yii, please pay attention to the PHP Chinese website!