How to apply Count on prefetched queryset in django?

Question

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.

Answer 1

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.

Answer 2

not tested but maybe it works

routines = Routine.objects.prefetch_related('activities').filter(activities__is_active=True).annotate(activity_count=Count('activities'))

Answer 3

Prefetch your related model and annotate the count while filtering as described in the aggregation documentation.

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