Opened 9 years ago

Closed 7 years ago

#2922 closed enhancement (fixed)

[patch] defining outer joins

Reported by: mir@… 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: UI/UX:

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)

extra_joins.diff (2.5 KB) - added by mir@… 9 years ago.
promised patch

Download all attachments as: .zip

Change History (12)

Changed 9 years ago by mir@…

promised patch

comment:1 Changed 9 years ago by mir@…

  • Resolution set to wontfix
  • Status changed from new to closed

comment:2 Changed 9 years ago by Jonathan Buchanan <jonathan.buchanan@…>

  • 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 Changed 9 years ago by jacob

  • Resolution wontfix deleted
  • Status changed from closed to reopened

comment:4 Changed 9 years ago by Michael Radziej <mir@…>

  • Keywords reopen removed
  • Needs documentation set
  • Needs tests set
  • Patch needs improvement set
  • Triage Stage changed from Unreviewed to 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.

comment:5 follow-up: Changed 9 years ago by mtredinnick

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 in reply to: ↑ 5 Changed 9 years ago by Michael Radziej <mir@…>

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 Changed 8 years ago by mtredinnick

  • Keywords qs-rf added

comment:8 Changed 8 years ago by anonymous

  • Cc sam@… added

comment:9 Changed 8 years ago by apollo13

  • Cc django@… added

comment:10 Changed 8 years ago by mtredinnick

  • Keywords qs-rf removed
  • Triage Stage changed from Design decision needed to 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 Changed 7 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from reopened to 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.

Note: See TracTickets for help on using tickets.
Back to Top