#33838: Querying issue with ForeignKeys where db_constraint=False
-------------------------------------+-------------------------------------
Reporter: Alex Bailey | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.0
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Alex Bailey:
Old description:
> 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 where both foo__name and bar__name == 'Alex'
> >>> 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.
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/33838#comment:1>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070181ee38cb5a-1c9b070a-12f8-41d0-a5f8-96a65eaa7c60-000000%40eu-central-1.amazonses.com.
