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.

symfony basics: form default values

This is some stuff for symfony beginners, who still want to learn symfony 1.4. You may set default form values for all kind of forms (including doctrine forms). Set one default value at a time:

class XxxForm extends BaseXxxForm
{
  public function configure()
  {
    $this->setDefault ('field', 'value');
  }
}

or set a whole array of them:

public function configure()
{
  $this->setDefaults(array(
    'field_1' => 'value_1',
    'field_2' => 'value_2',
    // ...
    'field_x' => 'value_x'
  ));
}

default values for new objects

Sometimes you want to set the default form values just before the object is created, because it'd be easier for the aplication user to fill in some data. For example, the owner/author of a blog post may be set default to the current logged in user - or the date of an event may be set to now - and so on. This can be achieved with the isNew method of the doctrine form class (lib/form/doctrine/XxxForm.class.php):

if ($this->isNew())
{
  $this->setDefault ('created_at'1, date('Y-m-d 00:00:00'));
  $this->setDefault ('created_by'2, sfContext::getInstance()->getUser()3->getId());
}

• note ¹: Timestampable Doctrine behavior used in this example,
• note ²: Signable Doctrine behavior used in this example,
• note ³: check this blog post to avoid using sfContext::getInstance().

moreover

You may implement whatever complex conditions you want your doctrine form to follow. Look at some of examples below:

• current time - php time function
• language/localization (default country when registering a new user) - use Accept-Language HTTP
• default settings set for a registered user - fetch individual user settings from database (doctrine query)
• last used item (category/product/etc.) - a user inserts or updates a large amount of data, when he choses a specific item (category/product) it can be saved in its session ($user->set/getAttribute()) - when another record is processed, last used item is used as default (which, again, lowers time needed for the user to work)

A well designed interface includes lots of form default values, so that users don't have to waste their time on picking up the same values over and over again.