Blog: Getting To Know Propel 1.5: Keeping An Aggregate Column Up To Date

The Propel Team – 29 April 2010

Propel 1.5 was released earlier this week, so it’s a good time to learn how to get the most of its new Query objects. Today’s exercise aims to keep an aggregate column up to date, and illustrates the use of ActiveRecord and Query hook methods.

The Model

The model is simple: for a poll widget, a PollQuestion and a PollAnswer class share a one-to-many relationship. The PollAnswer class features a NbVotes property, incremented each time a user votes for this question. The PollQuestion also needs a TotalNbVotes, which is the sum of the NbVotes of all the related PollAnswers, in order to display answer ratings as percentages. Let’s see how to manage this TotalNbVotes column automatically.

The Easy Part: Using ActiveRecord Hooks

Each time the PollAnswer’s NbVotes is incremented, the parent PollQuestion’s TotalNbVotes should be incremented as well. In fact, the use case is larger than than: each time a PollAnswer is added, deleted, or modified, the parent’s TotalNbVotes should be recalculated.

Since version 1.4, Propel offers hooks in the generated ActiveRecord model objects, so this is quite easy to implement:

class PollAnswer extends BasePollAnswer
{
    public function postSave(PropelPDO $con)
    {
        if ($parentQuestion = $this->getPollQuestion()) {
            $parentQuestion->updateNbVotes($con);
        }
    }

    public function postDelete(PropelPDO $con)  {
        if ($parentQuestion = $this->getPollQuestion()) {
            $parentQuestion->updateNbVotes($con);
        }
    }
}

Propel calls the postSave() hook each time a PollAnswer object is inserted or updated. Notice that both the postSave() and the postDelete() receive the current connection object, and use it. This is because these methods are called during a transaction, and the connection object should be the same throughout the whole transaction to let Propel and the database revert the transaction is something wrong occurs.

Good Old PDO To The Rescue

The task to keep the total vote count up to date is left to the PollQuestion object. This could be done using a ModelCriteria, but since the expected result is a scalar, there is no need to hydrate an ActiveRecord. So let’s use a raw PDO query instead. To keep a minimum of model abstraction, the column and table name should be represented by their class constants:

class PollQuestion extends BasePollQuestion
{
    public function updateNbVotes($con = null)
    {
        $sql = 'SELECT SUM(' . PollAnswerPeer::NB_VOTES . ') AS nb'
            . ' FROM ' . PollAnswerPeer::TABLE_NAME
            . ' WHERE ' . PollAnswerPeer::QUESTION_ID . ' = ?';

        $stmt = $con->prepare($sql);
        $stmt->execute(array($this->getId()));
        $this->setTotalNbVotes($stmt->fetchColumn());
        $this->save($con);
    }
}

The updateNbVotes() method executes one SELECT query, and if the result differs from the current TotalNbVotes, then the PollQuestion object gets updated.

So when a PollAnswer gets updated, its NbVotes is saved, then the PollAnswer::postSave() method fetches the parent PollQuestion, and calls PollQuestion::updateNbVotes() to calculate and persist the new TotalNbVotes. All in a single transaction.

That’s it for the easy part.

Using Query Hooks

There is a use case that hasn’t been addressed yet: What if a set of PollAnswer objects is deleted using PollAnswerQuery::delete(), rather than using individual PollAnswer::delete() calls? The previous changes wouldn’t be enough to update the TotalNbVotes in this case.

PollAnswerQuery::create()
    ->filterByBody('%TEMP%')
    ->delete();

It is necessary to use the preDelete() and postDelete() hooks of the PollAnswerQuery class for that. The preDelete() code must determine the PollAnswer objects concerned by the deletion, and from then on, keep the related PollQuestion objects. The postUpdate() method should iterate over this collection of PollQuestion objects, and call updateNbVotes() on each of them. Here is a first way to implement this:

class PollAnswerQuery extends BasePollAnswerQuery
{
    protected $pollQuestions = array();

    public function preDelete(PropelPDO $con) {
        $pollAnswerQuery = clone $this;
        $pollAnswers = $pollAnswerQuery
            ->joinWith('PollQuestion')
            ->find($con);

        foreach ($pollAnswers as $pollAnswer) {
            $this->pollQuestions[$pollAnswer->getQuestionId()] = $pollAnswer->getPollQuestion();
        }
    }

    public function postDelete($affectedRows, PropelPDO $con) {
        foreach ($this->pollQuestions as $pollQuestion) {
            $pollQuestion->updateVotesNb($con);
        }
        $this->pollQuestions = array();
    }
}

The preDelete() code reuses the current query object, but terminates with a find() rather than a delete(). joinWith() helps to reduce the number of SQL queries to 1, even though getPollQuestion() gets called in a loop afterwards.

Merging Queries

The preDelete() code creates a SQL query similar to:

SELECT poll_answer.*, poll_question.*FROM poll_answer
INNER JOIN poll_question ON poll_answer.question_id = poll_question.id
WHERE poll_answer.body LIKE '%TEMP%';

The PHP code iterates over the result of this query to retrieve a list of PollQuestion objects with no duplicates. But what is really needed there is a list of PollQuestion objects. There is no real need to pass by an intermediate list of PollAnswers.

But, if you use a PollQuestionQuery to get PollQuestion objects, how can it take the conditions applied to a PollAnswerQuery object? It’s quite simple: just merge the two query objects together. You can simply write the PollAnswerQuery::preDelete() method as follows:

public function preDelete(PropelPDO $con)
{
    $this->pollQuestions = PollQuestionQuery::create()
        ->joinPollAnswer()
        ->mergeWith($this)
        ->find($con);
}

The resulting SQL query is now:

SELECT poll_question.*FROM poll_question INNER JOIN poll_answerON poll_question.id = poll_answer.question_idWHERE poll_answer.body LIKE '%TEMP%';

Conclusion

Query objects are very reusable: you can clone them and use a different termination method, or merge them with another query. It brings reusability to queries the same way the ActiveRecord pattern brings reusability to row manipulation.

The example is not finished: the PollAnswerQuery method should also implement the preUpdate() and postUpdate() hooks to deal with update() queries that may alter the vote count of a list of PollAnswer objects.

And, besides polls, what was demonstrated here is a very common need: keep an column calculated with an aggregate function on a related table up to date. From the number of books of an author to the latest edition of the articles of a website, the requirement is very generic. To make the above code even more reusable, it must be packaged as a behavior. You will learn how to create such a aggregate_column behavior in a future session of "Getting To Know Propel 1.5".