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

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.

On Oct 8, 10:45 pm, Sebastian Goll <sebastian.g...@gmx.de> wrote:
> On Sat, 8 Oct 2011 16:30:37 -0500
> Javier Guerra Giraldez <jav...@guerrag.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Sat, Oct 8, 2011 at 8:18 AM, Michal Petrucha <michal.petru...@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?
>
> 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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.