How do I get something like repeat customers in Django

Question

My models look something like this:

class Customer(models.Model):
   name = models.CharField(max_length=100)

class Order(models.Model):
   customer = models.ForeignKey(Customer)
   date = models.DateField()
   total = models.DecimalField(max_digits=5, decimal_places=2)

I then have a queryset of orders:

from datetime import datetime

start_date = datetime(year=2009, month=6, day=1)
end_date = datetime(year=2009, month=11, day=1)
orders = Order.objects.filter(date__lte=end_date).filter(date__gte=start_date)

Now, I want to find out which customers made multiple orders between those times, how many orders they made, and what their average total is. I get the feeling that I should be using Django 1.1's new aggregation features, but I can't really wrap my head around it.

Answer 1

This would be a good use for Django 1.1's annotate() functionality, which is part of aggregateion. Specifically, you'll probably want to use the values() function.

Answer 2

Always base your query around the object in which you are primarily interested in:

repeat_customers = Customer.objects.annotate(order_count=Count('order'))\
                                   .filter(order_count__gt=1)

Then if you want to annotate with their totals (you could alternatively do this in the annotation above, I'm just separating the code for readability):

repeat_customers = repeat_customers.annotate(avg_total=Avg('order__total'))