[Django] #35677: Unexpected behaviour of Prefetch with queryset filtering on a through model

[Django]

#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 django-updates+unsubscr...@googlegroups.com.
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.