views:

100

answers:

2

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

+1  A: 

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/)

grucha
Thanks for the helpful response and sorry for the newbie question, but how would I then call this method from the view for each client, passing it back to the template from there? Thanks again, hopefully this well be helpful for other Django greenhorns.
Dan0
y, m = 2010, 10clients = []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 ;-)
grucha
It makes perfect sense now, thanks!
Dan0
A: 
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)

grucha