#35677: Unexpected behaviour of Prefetch with queryset filtering on a through
model
-------------------------------------+-------------------------------------
Reporter: David Glenck | Type:
| Uncategorized
Status: new | Component: Database
| layer (models, ORM)
Version: 5.1 | Severity: Normal
Keywords: Prefetch, | Triage Stage:
prefetch_related, many-to-many | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Currently prefetching many-to-many objects with a queryset that filters
based on fields in the through model, will return different results than
applying the same filter without prefetching.
Assume the following many-to-many relationship with a through model:
{{{
class Subscriber(models.Model):
name = models.CharField(max_length=255)
subscriptions = models.ManyToManyField('Subscription',
related_name='subscribers', through='Status')
class Subscription(models.Model):
provider_name = models.CharField(max_length=255)
class Status(models.Model):
subscriber = models.ForeignKey(Subscriber, related_name='status',
on_delete=models.CASCADE)
subscription = models.ForeignKey(Subscription, related_name='status',
on_delete=models.CASCADE)
is_active = models.BooleanField(default=True)
}}}
Populated with the following data:
{{{
subscriber1 = Subscriber.objects.create(name="Sofia")
subscriber2 = Subscriber.objects.create(name="Peter")
subscription1 = Subscription.objects.create(provider_name="ABC")
subscription2 = Subscription.objects.create(provider_name="123")
Status.objects.create(subscriber=subscriber1,
subscription=subscription1, is_active=True)
Status.objects.create(subscriber=subscriber1,
subscription=subscription1, is_active=True)
Status.objects.create(subscriber=subscriber1,
subscription=subscription2, is_active=False)
Status.objects.create(subscriber=subscriber2,
subscription=subscription1, is_active=True)
Status.objects.create(subscriber=subscriber2,
subscription=subscription2, is_active=True)
}}}
To get the active subscriptions of Sofia I can do this:
{{{
subscriber1.subscriptions.filter(status__is_active=True)
}}}
which gives me as expected twice the first subscription
Now, I try to prefetch the active subscriptions like this:
{{{
prefetched = Subscriber.objects.filter(pk__in=[subscriber1.pk,
subscriber2.pk]).prefetch_related(
Prefetch(
'subscriptions',
queryset=Subscription.objects.filter(status__is_active=True),
to_attr='active_subscriptions'
)
)
prefetched[0].active_subscriptions
}}}
But if I do this, I get queryset with 4 times instead of 2 times the first
subscription.
Looking into the source I found, that in the first case, the internal
filter of the related_descriptor is made "sticky", such that it will be
combined with the next filter that is applied, as if only one filter was
used. So it behaves equivalent to:
{{{
Subscription.objects.filter(subscribers=subscriber1.id,
status__is_active=True)
}}}
The prefetch on the other hand doesn't do any magic to stick the filters
together and it will behave like this:
{{{
Subscription.objects.filter(subscribers=subscriber1.id).filter(status__is_active=True)
}}}
which, as well documented, is behaving differently for many-to-many
relationships: https://docs.djangoproject.com/en/5.0/topics/db/queries
/#spanning-multi-valued-relationships
Ideally the first filter on the queryset would also stick to the internal
filter. Or at least there should be an easy way to change the behaviour,
when needed.
--
Ticket URL: <https://code.djangoproject.com/ticket/35677>
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/010701914d152c83-1c6cbc5f-3fc6-4962-be5a-822b16e8e72d-000000%40eu-central-1.amazonses.com.
