Opened 17 years ago

Closed 16 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: 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)

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

Download all attachments as: .zip

Change History (12)

by mir@…, 17 years ago

Attachment: extra_joins.diff added

promised patch

comment:1 by mir@…, 17 years ago

Resolution: wontfix
Status: newclosed

comment:2 by Jonathan Buchanan <jonathan.buchanan@…>, 17 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 Jacob, 17 years ago

Resolution: wontfix
Status: closedreopened

comment:4 by Michael Radziej <mir@…>, 17 years ago

Keywords: reopen removed
Needs documentation: set
Needs tests: set
Patch needs improvement: set
Triage Stage: UnreviewedDesign 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 by Malcolm Tredinnick, 17 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.

in reply to:  5 comment:6 by Michael Radziej <mir@…>, 17 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 Malcolm Tredinnick, 17 years ago

Keywords: qs-rf added

comment:8 by anonymous, 16 years ago

Cc: sam@… added

comment:9 by Florian Apolloner, 16 years ago

Cc: django@… added

comment:10 by Malcolm Tredinnick, 16 years ago

Keywords: qs-rf removed
Triage Stage: Design decision neededSomeday/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 Malcolm Tredinnick, 16 years ago

Resolution: fixed
Status: reopenedclosed

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