extending doctrine admin module: filtered sum

Scene from "Breakfast at Tiffany's" by Blake Edwards (1961)

Another post for symfony beginners. I'll show how to display a sum (or any other function) of all elements filtered in an admin module. All elements - meaning the ones displayed on the current page (list pagination) and all the rest which is not currently visible. This is going to be really easy.

action

Let's start with calculations. We need to get the sum of all filtered elements and pass it to the View. As this article is not about MVC design pattern, I'll just override executeIndex action and put the calculations code inside (to make it as easy as possible, though calculations should be done in model, not controller).

class xxxActions extends autoXxxActions
{
  public function executeIndex(sfWebRequest $request)
  {
    parent::executeIndex($request);
    $query = $this->buildQuery()->copy();
    $root_alias = $query->getRootAlias();
    $total_data = $query
      ->limit(0)
      ->select("SUM({$root_alias}.cash_total) AS sum")
      ->fetchArray();
    $this->total_count = $total_data[0]['sum'];
  }
}

As you can see, we've got a Xxx model which holds the cash_total: decimal column, representing a sum of money. The $this->buildQuery()->clone() part does all the magic - we have the query with all filters set by the user in the interface. We will only tell doctrine to calculate the sum of all filtered elements for us ($total_count variable will be available in the indexSuccess.php template). The ->limit(0) clears the SQL limit clause, of course.

templates

This part is boring, actually. We need to override two more files: fetch them from cache and put them in the module/template directory. These files are: indexSuccess.php in which the line

<?php include_partial('event/list', array('pager' => $pager, 'sort' => $sort, 'helper' => $helper)) ?>

should be replaced with:

<?php include_partial('event/list', array('pager' => $pager, 'sort' => $sort, 'helper' => $helper, 'total_count' => $total_count)) ?>

and the _list.php partial which should have few lines added:

<tfoot>
  <tr><!-- added code starts here -->
    <th colspan="6">
      w sumie: <?php echo Tools::priceFormat($total_count, true) ?>
    </th>
  </tr><!-- added code ends here -->
  <tr>
    <th colspan="6">
       <?php if ($pager->haveToPaginate()): ?>
         <?php include_partial('event/pagination', array('pager' => $pager)) ?>
       <?php endif; ?>

As you can see, it's trivial, yet useful.

The sum of all filtered elements is visible in the footer of the doctrine admin module list table, as you can see below, but you can put it anywhere you want (as long as it's in the index action templates):

other functions

Of course, you can use other aggregate functions, such as average, minumum or maximum element - just take a look at the MySQL documentation. You may also create your own functions.