How to do equivalent of LEFT JOIN ON in Django (returning all Left rows, when Right item may not exist)

[Attempting Django]

Hi guys, can anyone please give me an example of how to do equivalent
of a LEFT JOIN ON in Django?? I am trying to return every row from one
table on the left, with any matches in a foreign table on the right
(there will be only one or NULL match possible). The issue I'm having
is showing left table data when there is a NULL on the right. Thanks!

[Michal Petrucha]

Django does this by default on nullable ForeignKeys. All you need to
do is something like::

MyModel.objects.select_related('foreignkey_field')

Behind the scenes, Django does a JOIN. Its type (INNER od LEFT OUTER)
is decided based on whether the left-hand column (your ForeignKey
field) is allowed to be NULL or not.

Michal

[Javier Guerra Giraldez]

On Sat, Oct 8, 2011 at 8:18 AM, Michal Petrucha <michal....@ksp.sk> wrote:
> Behind the scenes, Django does a JOIN. Its type (INNER od LEFT OUTER)
> is decided based on whether the left-hand column (your ForeignKey
> field) is allowed to be NULL or not.

the weird part, is that is takes the same choice on reverse
relationships, which isn't necessarily appropriate.

for example, model A has a foreign key to B. when reading A records
with select_related, it makes sense that if the FK is NULLable, it
uses a LEFT OUTER join while if it's not NULLable, use an INNER join.
when I have a B object, it's _really_ handy to have the reverse
relationship set up for me; but what if i want to select those B
objects that doesn't have any A?

this works...

B.objects.filter(a_set__isnull=True)

but only if the FK from A to B has the null=True argument.

and if I want to make the FK from A to B non-NULLable? maybe an A
record doesn't make any sense without B, but B without any A does. so
i don't put (null=True); so in the query above, i get an INNER join
followed by the condition that a.id IS NULL, and the result set is
empty. :-(

the only solution i've found is to make the FK NULLable, even when it
doesn't make sense. I'd love to see a separate reverse_null=True
argument (by default reverse_null==null) on the FK, that would make
the reverse join a LEFT OUTER one while still prohibiting an A record
without B.

am i missing something?

--
Javier

[Sebastian Goll]

This is part of the erroneous behavior fixed by a proposed patch to #16715 [1]. In this ticket, several other model relations lead to the wrong join type. Not having reverse relations be null-able is one of the observations.

Maybe you, or some other developer, can review the proposed patch for this problem? The part of this mail, making reverse relations null-able is the more trivial part of the patch, and essentially a one-liner in django/db/models/sql/compiler.py. "new_nullable = f.null or None" in line 674 – as of r16928 – should be "new_nullable = True" since reverse relations should always be considered null-able. See the proposed patch (#16715) for details.

Sebastian.

[1] https://code.djangoproject.com/ticket/16715

[Attempting Django]

Thanks everyone for your comments.

I solved the problem, not very elegantly, but solved nevertheless:

q1 =
OurProduct.objects.select_related().exclude(productoverride__customer=1)
q2 = ProductOverride.objects.select_related().filter(customer=1)
from itertools import chain
customerpricelist = list(chain(q1, q2))

I now have different fieldnames in the DICT generated by Q1 and Q2
(for example: ourproduct.code and code - for what should be values in
the same field!). This caused a bit of a nasty looking TEMPLATE to
resolve to screen correctly but the result works great, implementation
a severe workaround!

Thanks again.