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.

SamSparx · Accepted Answer · 2023-07-05T21:11:03.653

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.

edited Jul 05 '23 at 21:11

answered Jul 04 '23 at 05:41

SamSparx

4,629
1
4
16

It gives FieldError "Cannot resolve keyword 'activity_for_count' into field. Choices are: activities, id, owner, owner_id" – Tanvir Ahmed Jul 04 '23 at 06:11
Updated answer with another approach. – SamSparx Jul 04 '23 at 11:45
No error now but routines with empty activities are still present. – Tanvir Ahmed Jul 05 '23 at 06:03
You're right - I can see where I went wrong too. Third time lucky - I think prefetch might be the wrong way to go. – SamSparx Jul 05 '23 at 07:45
Chaining .prefetch('activities') gives an attribute error but the following code snippet works: active_activities = Activity.objects.filter(is_active=True) routines = Routine.objects.filter(activities__in=active_activities).prefetch_related( Prefetch('activities', queryset=active_activities) ) As I'm using ``active_activities`` at two places one for filtering routines and one for filtering activities I'm a bit concerned about the performance. – Tanvir Ahmed Jul 05 '23 at 13:11
Typo on my part - use prefetch_related('activities'). – SamSparx Jul 05 '23 at 21:15

score 0 · Answer 2 · answered Jul 04 '23 at 07:00

0

not tested but maybe it works

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

answered Jul 04 '23 at 07:00

Dimitris Kougioumtzis

2,339
1
25
36

score 0 · Answer 3 · answered Jul 05 '23 at 09:51

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)
)

How to apply Count on prefetched queryset in django?

3 Answers3