Need a workaround to filter on related model and aggregated fields in Django

Posted by parxier on Stack Overflow See other posts from Stack Overflow or by parxier
Published on 2010-05-02T23:10:14Z Indexed on 2010/05/03 22:18 UTC
Read the original article Hit count: 253

Filed under:
|
|
|

I opened a ticket for this problem.

In a nutshell here is my model:

class Plan(models.Model):
 cap = models.IntegerField()

class Phone(models.Model):
 plan = models.ForeignKey(Plan, related_name='phones')

class Call(models.Model):
 phone = models.ForeignKey(Phone, related_name='calls')
 cost = models.IntegerField()

I want to run a query like this one:

Phone.objects.annotate(total_cost=Sum('calls__cost')).filter(total_cost__gte=0.5*F('plan__cap'))

Unfortunately Django generates bad SQL:

SELECT "app_phone"."id", "app_phone"."plan_id",
SUM("app_call"."cost") AS "total_cost"
FROM "app_phone"
INNER JOIN "app_plan" ON ("app_phone"."plan_id" = "app_plan"."id")
LEFT OUTER JOIN "app_call" ON ("app_phone"."id" = "app_call"."phone_id")
GROUP BY "app_phone"."id", "app_phone"."plan_id"
HAVING SUM("app_call"."cost") >=  0.5 * "app_plan"."cap"

and errors with:

ProgrammingError: column "app_plan.cap" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...."plan_id" HAVING SUM("app_call"."cost") >=  0.5 * "app_plan"....

Is there any workaround apart from running raw SQL?

© Stack Overflow or respective owner

Related posts about python

Related posts about django