Django filter on prefetch related queryset

Question

I'm using Django 2.2

I have many reverse related models to the User model and I want to get count from each model with different filters.

For example, I have a Relations model like

status = (
  ('Active', 'active')
  ('Inactive', 'inactive')
)

class Relation(models.Model):
  user = models.ForeignKey(User, related_name='relation')
  status = models.CharField(choices=status, default=ACTIVE)

Now I want to get the count and the queryset for each status separately for the user. For that I have defined the model methods inside the User model

def get_relation():
  return self.relation.all()

def get_active_relation(self):
  return self.relation().filter(status='active')

def get_inactive_relation():
  return self.relation().filter(status='inactive')

def get_active_count():
  return self.get_active_relation().count()

def get_inactive_count():
  return self.get_inactive_relaiton().count()

I have the user object as

user = User.objects.prefetch_related(
  'relation'
).get(pk=request.user.pk)

Now when I get the count, it executes an extra query for that

user.get_active_count()

How can I filter on the prefetch_related objects?

I found a use of lambda to get the max value from the prefetch_related in another SOF answer: https://stackoverflow.com/a/12609454/3719167

Is it possible to use lambda to filter the queryset as well?

Answer 1

According to the documentation, you can't filter already cached objects. However, you can filter them out at the time of getting the object using Prefetch. It is work in django 3.1 too. For example:

from django.db.models import Prefetch

user = User.objects.get(pk=request.user.pk).prefetch_related(
  Prefetch('relation', queryset=Relation.objects.filter(status='active'))
)

Answer 2

Have you tried filtering on the attributes of the relation?

user = User.objects.prefetch_related(
    'relation'
).filter(
    relation__status='active'
).get(pk=request.user.pk)