views:

194

answers:

1

Does anyone know of how I would, through the django ORM, produce a query that conditionally aggregated related models?

Let's say, for example, that you run a site that sells stuff, and you want to know how much each employee has sold in the last seven days. It's simple enough to do this over all sales:

q = Employee.objects.filter(type='salesman').annotate(total_sales = models.Sum('sale__total'))

assuming Employee and Sale models with a many-to-many relationship between them. OK, but now how would I go about constraining this to all sales for the last seven days (or any arbitrary time frame)? Does anyone know?

+2  A: 

Alright, I guess I didn't think this through very far. I didn't realize that filter handled things with a left join (though thinking on it, how else would it map to the db?), so the obvious answer is:

Employee.objects.filter(type='salesman').filter(sale__timestamp__gte = start_date)\
        .exclude(sale__timestamp__gte = end_date).annotate(...
fromClouds