#33838 closed Bug (duplicate)
Querying issue with ForeignKeys where db_constraint=False
Reported by: | Alex Bailey | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 4.0 |
Severity: | Normal | 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 (last modified by )
models.py
from django.db import models class Person(models.Model): name = models.CharField(max_length=25) class Book(models.Model): foo = models.ForeignKey(Person, db_constraint=False, on_delete=models.DO_NOTHING, related_name='foo') bar = models.ForeignKey(Person, db_constraint=False, on_delete=models.DO_NOTHING, related_name='bar')
setup
from .models import Book, Person alex = Person.objects.create(name='Alex') Book.objects.create(foo=alex, bar=alex) Book.objects.create(foo=alex, bar_id=999) Book.objects.create(foo_id=999, bar=alex)
shell
>>> from django.db.models import Q #Correct: Returns both books where foo__name == 'Alex' >>> Book.objects.filter(foo__name='Alex') <QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]> #Correct: Returns both books where bar__name == 'Alex' >>> Book.objects.filter(bar__name='Alex') <QuerySet [<Book: Book object (1)>, <Book: Book object (3)>]> #Only returns one book, expected to return all 3. >>> Book.objects.filter(Q(foo__name='Alex') | Q(bar__name='Alex')) <QuerySet [<Book: Book object (1)>]>
I am working with an existing DB where it is possible for a Person
to no longer exist, hence the lack of db_constraint
in the ForeignKey
.
Books
can refer to non-existent Persons
but it still seems like expected behavior would be for the queryset OR to return all 3 books. I believe this is coming from the Django ORM doing an INNER JOIN. The 3rd query should be a simple union of the first 2 queries.
All 3 books fit into the expected query of books WHERE foo__name = 'Alex' OR bar__name = 'Alex'
but the Django behavior does not reflect this.
Change History (3)
comment:1 by , 2 years ago
Description: | modified (diff) |
---|
comment:2 by , 2 years ago
comment:3 by , 2 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
Closing as duplicate of #33608.
While Django allows for database constraints to be omitted it doesn't make any assumptions with regards to database integrity implications.
In other words, if you break integrity expectations of ForeignKey
you must also adjust your code to do so and in your particular case if a Book
is still allowed to exist without a person the proper way to represent that is by setting null=True
on your field as you've discovered.
Adapting the ORM to special case db_constraint=False
like it does with null=True
has a few implications that should be debated on the mailing list before a decision is taken because it was not done when the feature was added.
I just noticed that adding
null=True
to the ForeignKeys produces the expected result on the last query:Django switched to using LEFT OUTER JOIN in this case. Should this same logic apply to ForeignKeys where
db_constraint=False
?