SumQuerySet

Jul 18, 2008

When implementing the model for a timetracking application, I was trying to sum the amount of hours for a certain queryset. Not succeeding by digging through the internals of django, I tried this — very simple — approach. It uses a custom Manager, which in turn uses a custom QuerySet.

At first, I tried to use the SQL-function SUM, but it was hard to tell the Django-ORM to use this standard SQL-function. I then decided to let python do it’s job instead.

The model has a field called hours, in my case a CharField (because I wanted to save time information in a “2:45” kind of format).

A custom Manager is used to return a queryset:

class SumManager(models.Manager):
    def get_query_set(self):
        return SumQuerySet(self.model)

Nothing fancy. get_query_set just returns a custom SumQuerySet:

class SumQuerySet(QuerySet):
    def sum_hours(self):
        return sum(float(entry.hours) for entry in self)

As you can see, this queryset has an additional method sum_hours, which is very simple. It just returns the sum of all entry.hours in the current queryset. This enables one to use a filter and then ask the queryset to return the sum of all the hours.

Then add the line objects = SumManager() to the model and all’s set!

Bonus:
You can also change the pagination-template to show the amount of hours in the current view in the admin:

{% load admin_list %}
{% load i18n %}
<p class="paginator">
{% if pagination_required %}
{% for i in page_range %}
    {% paginator_number cl i %}
{% endfor %}
{% endif %}
{{ cl.result_count }} {% ifequal cl.result_count 1 %}{{ cl.opts.verbose_name|escape }}{% else %}{{ cl.opts.verbose_name_plural|escape }}{% endifequal %}
{% if cl.query_set.sum_hours %}
&mdash; {{ cl.query_set.sum_hours }} hours total
{% endif %}
{% if show_all_url %}&nbsp;&nbsp;<a href="{{ show_all_url }}" class="showall">{% trans 'Show all' %}</a>{% endif %}
</p>

This shows, if the sum_hours is available in the current queryset, the number of hours of the records shown. (For this to work, you need to tell the admin you want to use your own SumManager as the default manager. Add the line manager = SumManager() to your Admin-inner class.)

JP van Oosten

SumQuerySet