djangodjango-querysetdjango-prefetch-related

How to apply Count on prefetched queryset in django?


To demonstrate my use case, I've devised a similar but simpler setup as follows:

class Student(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name


class Routine(models.Model):
    owner =  models.OneToOneField(Student, on_delete=models.CASCADE, related_name='routine')

    def __str__(self):
        return f"Routine | {self.owner}"


class Activity(models.Model):
    title = models.CharField(max_length=100)
    is_active = models.BooleanField(default=True)
    routine = models.ForeignKey(Routine, on_delete=models.CASCADE, related_name='activities')

    def __str__(self):
        return f"{self.title} | {'Active' if self.is_active else 'Expired'} | {self.routine.owner}"
routines = Routine.objects.all().prefetch_related(
    Prefetch('activities', queryset=Activity.objects.filter(is_active=True))
)

The queryset above will retrieve all routines and their related active activities. However, some routines might not have any associated active activities, and in such cases, their activities will be empty.

routines = Routine.objects.all().prefetch_related(
    Prefetch('activities', queryset=Activity.objects.filter(is_active=True))
).annotate(active_activity_count=Count("activities")).exclude(active_activity_count=0)

I updated the queryset to filter out routines with empty activities after prefetching active activities but this doesn't work. It seems like Count counts actual activities instead of prefetched activities.
Is there a way to make Count count prefetched activities?

Passing filter condition to Count makes it work:

routines = Routine.objects.all().prefetch_related(
    Prefetch('activities', queryset=Activity.objects.filter(is_active=True))
).annotate(active_activity_count=Count("activities", filter=Q(activities__is_active=True))).exclude(active_activity_count=0)

But I don't want it because the condition in my original case is a bit complex so I don't want to repeat it.


Solution

  • You could achieve the same effect with:

    active_activities = Activity.objects.filter(is_active=True)
    routines = Routine.objects.filter(activities__in=active_activities)
    

    You can then chain .prefetch_related('activities') if you need them.