Django: Ticket #2922: [patch] defining outer joinshttps://code.djangoproject.com/ticket/2922
<p>
(this is going to be discussed on the devel list)
</p>
<p>
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:
</p>
<pre class="wiki">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 &lt;= %(sort_value)d) '
'and (%(notes_table)s.release_behoben_id is null or rel2.sort_value &gt; %(sort_value)d))'
% {'sort_value': version, 'notes_table': Note._meta.db_table}],
).select_related()
</pre>en-usDjangohttps://www.djangoproject.com/s/img/site/hdr_logo.gifhttps://code.djangoproject.com/ticket/2922
Trac 1.2.2mir@…Tue, 17 Oct 2006 12:30:02 GMTattachment sethttps://code.djangoproject.com/ticket/2922
https://code.djangoproject.com/ticket/2922
<ul>
<li><strong>attachment</strong>
set to <em>extra_joins.diff</em>
</li>
</ul>
<p>
promised patch
</p>
Ticketmir@…Mon, 23 Oct 2006 19:57:30 GMTstatus changed; resolution sethttps://code.djangoproject.com/ticket/2922#comment:1
https://code.djangoproject.com/ticket/2922#comment:1
<ul>
<li><strong>status</strong>
changed from <em>new</em> to <em>closed</em>
</li>
<li><strong>resolution</strong>
set to <em>wontfix</em>
</li>
</ul>
<p>
doesn't make sense, see <a class="ext-link" href="http://groups.google.com/group/django-developers/browse_thread/thread/e071bb8bf57ec0a7"><span class="icon">​</span>http://groups.google.com/group/django-developers/browse_thread/thread/e071bb8bf57ec0a7</a>
</p>
TicketJonathan Buchanan <jonathan.buchanan@…>Sat, 03 Feb 2007 10:04:15 GMTkeywords sethttps://code.djangoproject.com/ticket/2922#comment:2
https://code.djangoproject.com/ticket/2922#comment:2
<ul>
<li><strong>keywords</strong>
<em>reopen</em> added
</li>
</ul>
<p>
As described in <a class="ext-link" href="http://groups.google.com/group/django-developers/browse_thread/thread/34c76768d34f3499"><span class="icon">​</span>http://groups.google.com/group/django-developers/browse_thread/thread/34c76768d34f3499</a> there are some situations where the ideal solution requires the ability to add multiple conditions to the ON clause of a LEFT OUTER JOIN.
</p>
<p>
As I can't see how to do this with using Q objects as suggested when this ticket was closed, could it be reopened?
</p>
TicketJacobTue, 06 Feb 2007 16:31:02 GMTstatus changed; resolution deletedhttps://code.djangoproject.com/ticket/2922#comment:3
https://code.djangoproject.com/ticket/2922#comment:3
<ul>
<li><strong>status</strong>
changed from <em>closed</em> to <em>reopened</em>
</li>
<li><strong>resolution</strong>
<em>wontfix</em> deleted
</li>
</ul>
TicketMichael Radziej <mir@…>Tue, 06 Feb 2007 17:07:52 GMTneeds_docs, needs_better_patch, needs_tests, stage changed; keywords deletedhttps://code.djangoproject.com/ticket/2922#comment:4
https://code.djangoproject.com/ticket/2922#comment:4
<ul>
<li><strong>keywords</strong>
<em>reopen</em> removed
</li>
<li><strong>needs_docs</strong>
set
</li>
<li><strong>needs_better_patch</strong>
set
</li>
<li><strong>needs_tests</strong>
set
</li>
<li><strong>stage</strong>
changed from <em>Unreviewed</em> to <em>Design decision needed</em>
</li>
</ul>
<p>
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.
</p>
<p>
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.
</p>
TicketMalcolm TredinnickSat, 10 Feb 2007 23:25:45 GMThttps://code.djangoproject.com/ticket/2922#comment:5
https://code.djangoproject.com/ticket/2922#comment:5
<p>
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.
</p>
<p>
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.
</p>
TicketMichael Radziej <mir@…>Mon, 12 Feb 2007 08:27:25 GMThttps://code.djangoproject.com/ticket/2922#comment:6
https://code.djangoproject.com/ticket/2922#comment:6
<p>
Replying to <a class="ticket" href="https://code.djangoproject.com/ticket/2922#comment:5" title="Comment 5">mtredinnick</a>:
</p>
<p>
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?
</p>
TicketMalcolm TredinnickThu, 13 Sep 2007 21:46:07 GMTkeywords sethttps://code.djangoproject.com/ticket/2922#comment:7
https://code.djangoproject.com/ticket/2922#comment:7
<ul>
<li><strong>keywords</strong>
<em>qs-rf</em> added
</li>
</ul>
TicketanonymousMon, 12 Nov 2007 15:48:05 GMTcc sethttps://code.djangoproject.com/ticket/2922#comment:8
https://code.djangoproject.com/ticket/2922#comment:8
<ul>
<li><strong>cc</strong>
<em>sam@…</em> added
</li>
</ul>
TicketFlorian ApollonerThu, 17 Jan 2008 21:51:35 GMTcc changedhttps://code.djangoproject.com/ticket/2922#comment:9
https://code.djangoproject.com/ticket/2922#comment:9
<ul>
<li><strong>cc</strong>
<em>django@…</em> added
</li>
</ul>
TicketMalcolm TredinnickMon, 04 Feb 2008 06:08:09 GMTstage changed; keywords deletedhttps://code.djangoproject.com/ticket/2922#comment:10
https://code.djangoproject.com/ticket/2922#comment:10
<ul>
<li><strong>keywords</strong>
<em>qs-rf</em> removed
</li>
<li><strong>stage</strong>
changed from <em>Design decision needed</em> to <em>Someday/Maybe</em>
</li>
</ul>
<p>
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.
</p>
<p>
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.
</p>
TicketMalcolm TredinnickSun, 06 Jul 2008 02:44:42 GMTstatus changed; resolution sethttps://code.djangoproject.com/ticket/2922#comment:11
https://code.djangoproject.com/ticket/2922#comment:11
<ul>
<li><strong>status</strong>
changed from <em>reopened</em> to <em>closed</em>
</li>
<li><strong>resolution</strong>
set to <em>fixed</em>
</li>
</ul>
<p>
Given what's possible with the <code>Query</code> 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 <code>Query.join()</code>, for example.
</p>
Ticket