Can't use CTE within a subquery?

[dimagi]

Can't use CTE within a subquery?

[millerdev]

@SebCorbin there are tests that use Subquery in conjunction with OuterRef, so that is a hopeful sign.

Perhaps try attaching the extra where clause element directly on the subquery, outside of the CTE

-    last_emissions_year=Subquery(qs[:1]),
+    last_emissions_year=Subquery(qs.filter(plant_id=OuterRef('pk'))[:1]),

Maybe that will change the meaning of your query in a bad way?

It's pretty hard for me to get a clear picture of what's going wrong without being able to run some code. Could you to write a failing test for django-cte or at least a query using the test models so I can reproduce the error?

Also, what version of Django are you using?

[SebCorbin]

Yes, attaching the filter clause is modifying the query in a bad way. I need the reference inside the CTE, and after having a look a the tests, the OuterRef is always used outside the With() object.

I've tried adding a test with the PR #16 but I'm not comfortable this is a real query (and of course it could be done way simpler).

My version of Django is 3.0.5.