[patch] defining outer joins

[mir@…]

(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()

[mir@…]

promised patch

[mir@…]

doesn't make sense, see ​http://groups.google.com/group/django-developers/browse_thread/thread/e071bb8bf57ec0a7

[Jonathan Buchanan <jonathan.buchanan@…>]

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?

[Michael Radziej <mir@…>]

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.

[Malcolm Tredinnick]

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.

[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?

[Malcolm Tredinnick]

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.

[Malcolm Tredinnick]

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.