Chaining With Clauses

[myusuf91]

I was wondering if its possible to chain with statements, like so:

with cte_1 as ( ... ),
cte_2 as ( select * from my_table t1 join cte_1 as t2 on t2.x = t1.y )
select
    *
from
    some_table t1
    join cte_1 as t2 on t2.x = t1.y
    join cte_2 as t3 on t3.a = t1.b

Currently, when I try chaining using multiple join or with_cte, I end up getting cte_1 nested within cte_2.

I have tried the following (but it does not work):

cte_1 = With(
    Model1.objects.filter(**filters)
)

cte_2 = cte_1.join(Model2, field1=cte_1.col.field2).with_cte(cte_1).filter(**filters2)

final_query = cte_2.join(Model3, field1=cte_2.col.field2).with_cte(cte_2).filter(field2=cte_1.col.field2)

[millerdev]

Does test_named_ctes have an example of what you're looking for?

I think you want something like

cte_1 = With(Model1.objects.filter(**filters))
cte_2 = cte_1.join(Model2, field1=cte_1.col.field2).filter(**filters2)

final_query = (
    cte_2.join(Model3, field1=cte_2.col.field2)
    .with_cte(cte_1)
    .with_cte(cte_2)
    .filter(field2=cte_1.col.field2)
)

[myusuf91]

The test_named_ctes function really helped. Thanks! But if the ctes are unrelated to each other, but related to only the final query, like so:

with cte_1 as ( select * from a ),
cte_2 as ( select * from b )
select
    *
from
    some_table t1
    left join cte_1 as t2 on t2.x = t1.y
    left join cte_2 as t3 on t3.x = t1.z

Then the above method does not work because we are allowed only one join statement and subsequent filters. Even if I provide the join statement with the arg. _join_type=LOUTER, then the subsequent filter clauses create an INNER JOIN in the resultant query.

I have tried the following (but it fails with error missing FROM-clause entry for table ...:

Model3.objects.filter(...).with_cte(cte_1).with_cte(cte_2).annotate(
    # need the query to create left join here
    aggr_cte1=Case(
        When(user_id=cte1.col.user_id, then=cte1.col.count),
        default=0,
        output_field=IntegerField()
    ),
    # need the query to create left join here
    aggr_cte2=Case(
        When(user_id=cte2.col.user_id, then=cte2.col.count),
        default=0,
        output_field=IntegerField()
    )
)

The CTE does not allow chaining multiple left joins to the final query. Any thoughts ?

[myusuf91]

OK, I found a way to chain multiple left joins, like so (bold code):

    cte_1.join(
        cte_2.join(
            Model3, user_id=cte_2.col.agent_id, _join_type=LOUTER
        ), user_id=cte_1.col.agent_id, _join_type=LOUTER
    ).annotate(
        aggr_1=cte_1.col.calls_count,
        aggr_2=cte_2.col.calls_count,
    ).with_cte(
        cte_1
    ).with_cte(
        cte_2
    )

Say, if I had 5 such ctes, it would really obfuscate the code. Nesting joins works, but is there a more straight-forward way to do this?

[millerdev]

I don't know of an alternative. Do you have an example of syntax would you prefer? Pull requests are welcome.