Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Joins don't use aliases in subqueries #2

Closed
pwfff opened this issue Mar 27, 2018 · 6 comments
Closed

Joins don't use aliases in subqueries #2

pwfff opened this issue Mar 27, 2018 · 6 comments

Comments

@pwfff
Copy link

pwfff commented Mar 27, 2018

See #1 for a possible solution.

@millerdev
Copy link
Contributor

@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
Copy link
Author

pwfff commented Apr 10, 2018

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
Copy link
Author

pwfff commented Jun 18, 2018

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
Copy link
Author

pwfff commented Jun 18, 2018

I have an attempt at a fix in #4.

@pwfff pwfff changed the title Need to be able to do outer joins Joins don't use aliases in subqueries Jun 18, 2018
@millerdev
Copy link
Contributor

millerdev commented Jun 18, 2018

@pwfff Is this issue resolved now?

@pwfff
Copy link
Author

pwfff commented Jun 19, 2018

Yes, with #4. Thank you!

@pwfff pwfff closed this as completed Jun 19, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants