Filtering Annotations in Django

Question

I am annotating a QuerySet with a Count, but i only want to count certain objects, so I'm using a filter:

    objects = Parent.objects.annotate(num_children=Count('child',filter=Q(deleted_at=None)))

The Filter is not working, the count for every object seems to be ignoring the filter, i.e. it also counts the children which have

deleted_at != None

The model:

class child(models.Model):
    parent = models.ForeignKey('project.Parent', on_delete=models.CASCADE)
    deleted_at = models.DateTimeField(blank=True, null=True)

Does anybody have a solution? Thanks in adcance.

Edit

Using Q(deleted_at__isnull=True) is not working either. Strangely with ~Q(deleted_at=None) I get the expected Behaviour...

Answer 1

TL;DR

When you filtering with Q(...) objects, the lookups must be provided with respect to the Parent model.

So, it should be filter=Q(child__deleted_at=None) instead of filter=Q(deleted_at=None)

objects = Parent.objects.annotate(
    num_children=Count('child', filter=Q(child__deleted_at=None))
)

---

Long Answer

To reproduce the error/issue behaviour, I created two simple models as below,

class Parent(models.Model):
    name = models.CharField(max_length=50)

    class Meta:
        db_table = 'parent_table'

    def __str__(self):
        return self.name


class Child(models.Model):
    parent = models.ForeignKey(Parent, on_delete=models.CASCADE, related_name='children')
    deleted_at = models.DateTimeField(blank=True, null=True)

    class Meta:
        db_table = 'child_table'

    def __str__(self):
        return f'{self.parent} -- {self.deleted_at}'

Notes:

1. I have set related_name='children', which more "meaningful" while querying reverse relations. If you're not familiar with the related_name, read more

   a. What is related_name in Django -- (SO Post)

   b. Following backwards relationship -- (Django doc)

   c. ForeignKey.related_name -- (Django doc)

2. I have set db_table, coz, I am going to query the database using RawSQL for some reason. You don't have to set it.

Now, I have populated the database with a few dummy data and the result is as follows,

Then I opened my Django shell and run the following query,

In [2]: from django.db.models import Q,Count                                                                                                                                                                       

In [3]: parent_objects = Parent.objects.annotate( 
   ...:     num_children=Count('children', filter=Q(children__deleted_at=None)) 
   ...: )                                                                                                                                                                                                          

In [4]: for parent in parent_objects: 
   ...:     print(parent.id, '--', parent.name, ' :: ', parent.num_children) 
   ...:                                                                                                                                                                                                            
1 -- parent-1  ::  2
2 -- parent-2  ::  0
3 -- parent-3  ::  1

OR, the raw SQL view,