Opened 15 years ago

Closed 15 years ago

Last modified 15 years ago

#11347 closed (wontfix)

isnull=False returns incorrect results for reverse relations

Reported by: mrts Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Preliminaries

Assume the following model:

from django.db import models

class A(models.Model):
    name = models.CharField(max_length=10)

    def __unicode__(self):
        return self.name

class B(models.Model):
    a = models.ForeignKey(A)
    name = models.CharField(max_length=10)

    def __unicode__(self):
        return self.name

and the following sample data:

>>> from reverserelation.models import A, B
>>> a1 = A.objects.create(name='first')
>>> a2 = A.objects.create(name='second')
>>> b1 = B.objects.create(a=a1, name='x')
>>> b2 = B.objects.create(a=a1, name='y')
>>> b3 = B.objects.create(a=a1, name='z')

The problem

Filtering A objects that have no associated Bs works as expected:

>>> A.objects.filter(b__isnull=True)
[<A: second>]

The opposite, filtering A objects that have at least one B that refers to it, doesn't.

Expected:

>>> A.objects.filter(b__isnull=False)
[<A: first>]

Got:

>>> A.objects.filter(b__isnull=False)
[<A: first>, <A: first>, <A: first>]

Why is this happening:

>>> A.objects.filter(b__isnull=False).query.as_sql()
('SELECT "reverserelation_a"."id", "reverserelation_a"."name"
 FROM "reverserelation_a"
INNER JOIN "reverserelation_b" ON ("reverserelation_a"."id" = "reverserelation_b"."a_id")
WHERE "reverserelation_b"."id" IS NOT NULL', ())

A SELECT DISTINCT is required in this case.

Change History (3)

comment:1 by mrts, 15 years ago

As this can be achieved with distinct(), the ticket can be wontfixed, i.e. the following works:

>>> A.objects.filter(b__isnull=False).distinct()
[<A: first>]

comment:2 by Alex Gaynor, 15 years ago

Resolution: wontfix
Status: newclosed

When you traverse a one-to-many relation in a way that can generate duplicate results it is your responsibility to use distinct().

comment:3 by mrts, 15 years ago

Indeed, but as the need to use explicit distinct() wasn't obvious to me at first glance, but the need to pick up objects that have at least one other thing referring to them seems (the infamous generalization) quite common, it should perhaps be documented somewhere around .filter()?

P.S. Regarding the seems common remark:

  • display authors that have written at least one book (assuming books have references to authors)
  • display goods that have been ordered at least once (assuming order/order items have references to goods)

etc.

Note: See TracTickets for help on using tickets.
Back to Top