#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 B
s 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 , 15 years ago
comment:2 by , 15 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
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 , 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.
As this can be achieved with
distinct()
, the ticket can be wontfixed, i.e. the following works: