id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
35677	Unexpected behaviour of Prefetch with queryset filtering on a through model	David Glenck	Simon Charette	"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."	Bug	closed	Database layer (models, ORM)	5.1	Normal	fixed	Prefetch, prefetch_related, many-to-many	Simon Charette Thiago Bellini Ribeiro	Ready for checkin	1	0	0	0	0	0