Joins don't use aliases in subqueries

[dimagi]

Joins don't use aliases in subqueries

[millerdev]

@pwfff With.join() has been reimplemented to use real joins now, and there is a secret parameter that you can pass to specify a LEFT OUTER JOIN. Example usage:

from django.db.models.sql.constants import LOUTER

query = cte.join(Region, name=cte.col.name, _join_type=LOUTER)

approximate SQL:

SELECT ...
FROM region
LEFT OUTER JOIN cte ON region.name = cte.name

Fair warning: this is an undocumented feature, and may change in the future. Also be careful: the join may be automatically changed to an INNER JOIN if you subsequently (after calling cte.join(...)) do other things like add filters involving joins to the queryset.

If you're satisfied with this I think we can close #1. Let me know what you think.

[pwfff]

It works as well as the version I had, but I'm still running into this issue:

django.db.utils.ProgrammingError: invalid reference to FROM-clause entry for table "share"
LINE 1: ...LEFT OUTER JOIN "my_table" V8 ON V0."foo_id" = ("my_table"."j...
                                                             ^
HINT:  Perhaps you meant to reference the table alias "v8".

I'll see if I can get a simple reproduction for you.

[pwfff]

Seems to happen when doing something like Foo.objects.filter(id__in=my_joined_queryset_with_ctes). Everything in the subquery gets an extra alias, but it doesn't resolve.

[pwfff]

I have an attempt at a fix in #4.

[millerdev]

@pwfff Is this issue resolved now?

[pwfff]

Yes, with #4. Thank you!