Aggregate Column Behavior

The aggregate_column behavior keeps a column updated using an aggregate function executed on a related table.

Basic usage

In schema.xml, use the <behavior> tag to add the aggregate_column behavior to a table. You must provide parameters for the aggregate column name, the foreign table name, and the aggregate expression. For instance, to add an aggregate column keeping the comment count in a post table:

<table name="post">
  <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
  <column name="title" type="varchar" required="true" primaryString="true" />
  <behavior name="aggregate_column">
    <parameter name="name" value="nb_comments" />
    <parameter name="foreign_table" value="comment" />
    <parameter name="expression" value="COUNT(id)" />
  </behavior>
</table>
<table name="comment">
  <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
  <column name="post_id" type="integer" />
  <foreign-key foreignTable="post" onDelete="cascade">
    <reference local="post_id" foreign="id" />
  </foreign-key>
</table>

Rebuild your model, and run the table creation SQL again. The model now has an additional nb_comments column, of type integer by default. Each time a record in the foreign table is added, modified, or removed, the aggregate column is updated:

$post = new Post();
$post->setTitle('How Is Life On Earth?');
$post->save();
echo $post->getNbComments(); // 0

$comment1 = new Comment();
$comment1->setPost($post);
$comment1->save();
echo $post->getNbComments(); // 1

$comment2 = new Comment();
$comment2->setPost($post);
$comment2->save();
echo $post->getNbComments(); // 2

$comment2->delete();
echo $post->getNbComments(); // 1

The aggregate column is also kept up to date when related records are modified through a query object:

CommentQuery::create()
    ->filterByPost($post)
    ->delete();
echo $post->getNbComments(); // 0

Customizing the aggregate calculation

Any aggregate function can be used on any of the foreign columns. For instance, you can use the aggregate_column behavior to keep the latest update date of related comments, or the total votes on comments. You can even keep several aggregate columns on a single table:

<table name="post">
  <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
  <column name="title" type="varchar" required="true" primaryString="true" />
  <behavior name="aggregate_column">
    <parameter name="name" value="nb_comments" />
    <parameter name="foreign_table" value="comment" />
    <parameter name="expression" value="COUNT(id)" />
  </behavior>
  <behavior name="aggregate_column">
    <parameter name="name" value="last_comment" />
    <parameter name="foreign_table" value="comment" />
    <parameter name="expression" value="MAX(created_at)" />
  </behavior>
  <behavior name="aggregate_column">
    <parameter name="name" value="total_votes" />
    <parameter name="foreign_table" value="comment" />
    <parameter name="expression" value="SUM(vote)" />
  </behavior>
</table>
<table name="comment">
  <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
  <column name="post_id" type="integer" />
  <foreign-key foreignTable="post" onDelete="cascade">
    <reference local="post_id" foreign="id" />
  </foreign-key>
  <column name="created_at" type="TIMESTAMP" />
  <column name="vote" type="integer" />
</table>

The behavior adds a computeXXX() method to the Post class to compute the value of the aggregate function. This method, called each time records are modified in the related comment table, is the translation of the behavior settings into a SQL query:

// in om/BasePost.php
public function computeNbComments(PropulsionPDO $con)
{
    $stmt = $con->prepare('SELECT COUNT(id) FROM `comment` WHERE comment.POST_ID = :p1');
    $stmt->bindValue(':p1', $this->getId());
    $stmt->execute();

    return $stmt->fetchColumn();
}

You can override this method in the model class to customize the aggregate column calculation.

Customizing the aggregate column

By default, the behavior adds one column to the model. If this column is already described in the schema, the behavior detects it and doesn’t add it a second time. This can be useful if you need a custom type or phpName for the aggregate column:

<table name="post">
  <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true" />
  <column name="title" type="varchar" required="true" primaryString="true" />
  <column name="nb_comments" phpName="CommentCount" type="integer" />
  <behavior name="aggregate_column">
    <parameter name="name" value="nb_comments" />
    <parameter name="foreign_table" value="comment" />
    <parameter name="expression" value="COUNT(id)" />
  </behavior>
</table>