Django: Prefetch on through table with filter

Question

I have the following models:

class Person(models.Model):
    name = models.CharField(max_length=255)

class Group(models.Model):
    name = models.CharField(max_length=255)
    members = models.ManyToManyField(Person, through="Membership")

class Membership(models.Model):
    group = models.ForeignKey(Group, on_delete=models.CASCADE, related_name="membership")
    person = models.ForeignKey(Person, on_delete=models.CASCADE, related_name="membership")
    is_active = models.BooleanField(null=True, blank=True)

The is_active on membership denotes whether a person is still a part of the group. I am trying to query groups and prefetch all active members. The script that I am using is

from sample.models import Person, Group
from django.db.models import Prefetch
from django.db import connection, reset_queries
reset_queries()
qs = Group.objects.prefetch_related(
    Prefetch(
        "members", queryset=Person.objects.filter(membership__is_active=True),
    )
)
print(list(qs.all()))
print(len(connection.queries)) # Should be two queries

However, the query that gets generated for the prefetch is

SELECT 
  ("sample_membership"."group_id") AS "_prefetch_related_val_group_id", 
  "sample_person"."id", 
  "sample_person"."name" 
FROM 
  "sample_person" INNER JOIN "sample_membership" ON ( "sample_person"."id" = "sample_membership"."person_id")
  INNER JOIN "sample_membership" T3 ON ( "sample_person"."id" = T3."person_id") 
WHERE "sample_membership"."is_active" AND T3."group_id" IN (1, 2)

There are two joins on sample_membership and only one of them has the is_active filter. (This is an abstracted version of what I am trying to solve - but the essence remains the same - having the ability to add filters on the m2m relationship when prefetch_related is used)

Edit: I have a serializers.py file as

from rest_framework import serializers
from .models import Person, Group
class PersonSerializer(serializers.ModelSerializer):
    class Meta:
        model = Person
        fields = '__all__'

class GroupSerializer(serializers.ModelSerializer):
    members = PersonSerializer(many=True)
    class Meta:
        model = Group
        fields = '__all__'

In the output of the group serializer, I am trying to get members with an active membership. A sample script to test out the serializer output would be

from sample.models import Person, Group, Membership
from sample.serializers import GroupSerializer
from django.db.models import Prefetch
from django.db import connection, reset_queries
reset_queries()
qs = Group.objects.prefetch_related(
    Prefetch(
        "membership",
      queryset=Membership.objects.filter(is_active=True).select_related("person"),
    )
)
for q in list(qs.all()):
    gs = GroupSerializer()
    gs.to_representation(instance=q)

Saxtheowl · Answer 1 · 2023-04-18T19:31:44.533

Filter the Membership instances and then select the related Person instances instead of filtering the Person instances in the queryset.

edit: we could use anotate for the serializer

from django.db.models import Prefetch
from django.db.models.functions import ArrayAgg
from sample.models import Person, Group, Membership
from django.db.models import Q

# Reset queries
from django.db import connection, reset_queries
reset_queries()

# Define the prefetch queryset
prefetch_qs = Membership.objects.filter(is_active=True).select_related("person")

# Annotate the Group queryset with the active members
qs = Group.objects.prefetch_related(
    Prefetch("membership_set", queryset=prefetch_qs, to_attr="active_memberships")
).annotate(
    active_member_names=ArrayAgg("membership_set__person__name", filter=Q(membership_set__is_active=True))
)

# Fetch the groups
groups = list(qs.all())

# Print results
print(groups)
print(len(connection.queries))
print(connection.queries[-2].values())
print(connection.queries[-1].values())

for group in groups:
    print(f"Active members in group {group.name}:")
    print(", ".join(group.active_member_names))

is there a way to have the members available on the top queryset so that this can be used directly in a serializer ? — willowherb, Apr 18 '23 at 10:38
I updated the annotation to be `active_members = ArrayAgg("membership__person", filter=Q(membership__is_active=True))` and the serializer to have `members = PersonSerializer(many=True, source="active_members")` However, the following snippet still does not work. `for q in list(qs.all()): gs = GroupSerializer() gs.to_representation(instance=q)` — willowherb, Apr 19 '23 at 07:37

Django: Prefetch on through table with filter

1 Answers1