Django: Summing values of records grouped by foreign key

Question

Hi there

In django, given the following models (slightly simplified), I'm struggling to work out how I would get a view including sums of groups

    class Client(models.Model):
        api_key = models.CharField(unique=True, max_length=250, primary_key=True)
        name = models.CharField(unique=True, max_length=250)

    class Purchase(models.Model):
        purchase_date = models.DateTimeField()
        client = models.ForeignKey(SavedClient, to_field='api_key')
        amount_to_invoice = models.FloatField(null=True)

For a given month, I'd like to see e.g.

April 2010

For Each Client that purchased this month:
*   CLient:  Name
*   Total amount of Purchases for this month
*   Total cost of purchases for this month

 For each Purchase made by client:
  * Date
  * Amount
  * Etc

I've been looking into django annotation, but can't get my head around how to sum values of a field for a particular group over a particular month and send the information to a template as a variable/tag.

Any info would be appreciated

Answer 1

You could use aggregate instead of annotate: http://docs.djangoproject.com/en/dev/topics/db/aggregation/#topics-db-aggregation

It could be handy to add a Client method:

class Client(models.Model):
    api_key = models.CharField(unique=True, max_length=250, primary_key=True)
    name = models.CharField(unique=True, max_length=250)

    def get_purchases_month(self, y, m):
        return self.purchase_set.filter(purchase_date__year=y, purchase_date__month=m).aggregate(purchase_sum=Sum("amount_to_invoice"))

You can't use it directly from the template as there's no way to pass parameters from there. At least it would be easy to use in some view (for example www.domain.com/yourview/2010/03/)

Answer 2

y, m = 2010, 10
clients = []
for c in Client.objects.all():
  clients.append({'client':c, 'purchases':c.get_purchases_month(y, m)})

And you pass clients list to your template and loop trough it there. It's not quite effective way but simple ;-)

{% for c in clients %}
  {{c.client.name}}
  {{c.purchases_month.purchase_sum}}
  {{c.purchases_month.all|length}}
{% endfor %}

purchases_month.all because it's a queryset and you have to use all() to get items, and `|length' to count these items

(had to put as another answer because code in comment apparently can't be formatted)