Ability to query CTE without joining

[dimagi]

Ability to query CTE without joining

[millerdev]

It looks like the given example can be done without a CTE

(
    Purchase.objects
    .filter(...)
    .aggregate(total_thing=Sum("thing"))
)

However, it's hard to tell since I cannot see the query constructed by get_purchases(profile, start_date, end_date). Can you provide more detail to show why you need to use a CTE?

[warmwaffles]

It looks like the given example can be done without a CTE

I was trying to do a contrived example as short as possible to try and get the point across.

So, the reason I use a CTE is to avoid a nasty nested query and provide postgres a better hint as to what indexes to use.

I have some more complex tables I have to join into a CTE then do an aggregate on. Django's aggregate does not provide a clean way for me to filter on the annotations and then inspect the query for an explain analyze.

I can't really paste the exact code in here as it is a few hundred lines and my employer would not be too happy 😛

[warmwaffles]

I did find it useful that the join method exposes _join_type was very useful for a LOUTER join.

[millerdev]

I think the problem is that there is no way (with the Django ORM) to add the CTE to the FROM clause except with a JOIN. Feel free to re-open this issue if you can come up with a simplified example that has enough moving parts to replicate the issue. Preferably, make the example using the test models.