[Answered ]-Use Django Subquery as FROM table or CTE in order to have a Window over an aggregate?

by

1👍

I’ve been struggling with a similar problem and just found the django-cte project which seems to confirm that the out-of-the-box ORM does not have the needed support, and offers a way forward using its With.

Using it, I believe a simplified version of your problem could be solved with something like this:

from django_cte import With

class Cost(Model):
    # pre-req!
    objects = CTEManager()
    year = IntegerField()
    month = IntegerField()
    cost = DecimalField()

cte = With(
    Cost.objects
    .values("year", "month", "month_total")
    .annotate(month_total=Sum("cost"))
)

innerq = cte.join(Cost, region=cte.col.year).with_cte(cte)...

(My use case is a bit different than yours). See the docs for more.

👤Shaheed Haque

Leave a comment