Opened 18 years ago
Closed 16 years ago
#2922 closed enhancement (fixed)
[patch] defining outer joins
Reported by: | Owned by: | nobody | |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | |
Severity: | normal | Keywords: | |
Cc: | sam@…, django@… | Triage Stage: | Someday/Maybe |
Has patch: | yes | Needs documentation: | yes |
Needs tests: | yes | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description
(this is going to be discussed on the devel list)
I was looking for an easy way to define extra outer joins. It turned out that it's easy to extend the QuerySet.extra method with a 'joins' argument used like this:
notes = Note.objects.select_related().extra( joins=['left outer join %s rel1 on rel1.id=%s.release_beginn_id' % (Release._meta.db_table, Note._meta.db_table), 'left outer join %s rel2 on rel2.id=%s.release_behoben_id' % (Release._meta.db_table, Note._meta.db_table)], where=['((%(notes_table)s.release_beginn_id is null or rel1.sort_value <= %(sort_value)d) ' 'and (%(notes_table)s.release_behoben_id is null or rel2.sort_value > %(sort_value)d))' % {'sort_value': version, 'notes_table': Note._meta.db_table}], ).select_related()
Attachments (1)
Change History (12)
by , 18 years ago
Attachment: | extra_joins.diff added |
---|
comment:1 by , 18 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
doesn't make sense, see http://groups.google.com/group/django-developers/browse_thread/thread/e071bb8bf57ec0a7
comment:2 by , 18 years ago
Keywords: | reopen added |
---|
As described in http://groups.google.com/group/django-developers/browse_thread/thread/34c76768d34f3499 there are some situations where the ideal solution requires the ability to add multiple conditions to the ON clause of a LEFT OUTER JOIN.
As I can't see how to do this with using Q objects as suggested when this ticket was closed, could it be reopened?
comment:3 by , 18 years ago
Resolution: | wontfix |
---|---|
Status: | closed → reopened |
comment:4 by , 18 years ago
Keywords: | reopen removed |
---|---|
Needs documentation: | set |
Needs tests: | set |
Patch needs improvement: | set |
Triage Stage: | Unreviewed → Design decision needed |
Jonathan, any ideas from your side according to the syntax? Though I use the patch above, I don't really like it. And the patch itself is not very good, it should use queryset.joins instead of introducing just another attribute _joins.
Personally, I'd rather have a way to turn an arbitrary sql statement into a QuerySet (provided that the statement selects all fields of model and each result row represents one model, of course). There are lots of cases when the ORM hits its limits.
follow-up: 6 comment:5 by , 18 years ago
Prior to 1.0, I plan on finish the refactoring of QuerySet so that the query itself is pulled out into a separate class (it will be an attribute inside QuerySet). This new Query class will contain attributes for the select fields, the tables to select (and their join types and aliases), as well as where clauses, having, group by, etc (the latter two not being used by Django, but available for developers who might want to use them). This object will be available after you have created the QuerySet and before it gets turned into SQL to query the database. At that point you can poke in any extra tables you want to join against and so. You could even create a subclass of QuerySet that used an extended version of this class to make such modifications easier.
So, a bunch of these "tweaking SQL" problems should become easier shortly. It should be a transparent change for people who don't want to use the extra stuff, but may destabilise things briefly, so I'm waiting for the post-0.96 period before landing any code.
comment:6 by , 18 years ago
Replying to mtredinnick:
I'm finding, the more I use Django's ORM, that a way to turn an arbitrary sql select command into a QuerySet (provided it contains all the fields of a model) would be the best idea to solve all the non-standard queries. ORMs that try to solve everything seem to get more complicated than writing custom sql ... But we should discuss this on the list. Would you like to fan out your ideas on this refactoring there?
comment:7 by , 17 years ago
Keywords: | qs-rf added |
---|
comment:8 by , 17 years ago
Cc: | added |
---|
comment:9 by , 17 years ago
Cc: | added |
---|
comment:10 by , 17 years ago
Keywords: | qs-rf removed |
---|---|
Triage Stage: | Design decision needed → Someday/Maybe |
Modifying join types (and adding extra joins/tables) is all possible via custom Q-like objects on the queryset-refactor branch (although the branch isn't ready for testing yet, the main querying functionality is there). So this doesn't need any core changes.
It's not clear that this needs to be included in core and should probably be developed externally first, in any case. Moving to someday/maybe for now. It's not a blocker for the queryset-refactor branch.
comment:11 by , 16 years ago
Resolution: | → fixed |
---|---|
Status: | reopened → closed |
Given what's possible with the Query
class in core now, I don't think anything extra is needed here if people really want to do something like this. Something like Michael's original example is possible by calling Query.join()
, for example.
promised patch