CODEWITHSUNDEEP
pythondjangodjango-models
César Muñoz
César Muñoz

Reputation: 645

Django Count filter annotation not working

I have two models 'ModelParent' and 'ModelChild', where 'ModelParent' has many 'ModelChild', here is my models structure:

class ModelParent(models.Model):
    ... some params ...


class ModelChild(models.Model):
    TYPES = (
        (1, 'First'),
        (2, 'Second'),
    )
    parent = models.ForeignKey(ModelParent, on_delete=models.CASCADE, related_name='childs')
    type = models.SmallIntegerField(choices=TYPES, default=0)

Currently, there's only one 'ModelChild' in the database who belongs to the only 'ModelParent' currently in the database, the 'type' value of the 'ModelChild' is equal to '1', I'm getting the 'ModelParent' object and I need to aggregate to it the count of his 'childs' where type is '1' and also the count of his 'childs' where type is '2', this is the way I'm trying to do so:

queryset = ModelParent.objects \
        .annotate(first_count=Count('childs', filter=Q(childs__type=1))) \
        .annotate(second_count=Count('childs', filter=Q(childs__type=2))).get(pk=1)

The query doesn't throw any errors but when looking at the response, the values for both annotations are '1', when it should be '1' only for 'first_count' and '0' for 'second_count'.

I have also noticed that no matter what value I set to 'childs__type' within the filter "filter=Q(childs__type=1)", the result is always the same, I can set it like this for example: 'childs__type=10' and still the count is equal to '1'.. it is like the entire 'filter' param is being ignored.

Upvotes: 2

Views: 1812

Answers (1)

César Muñoz
César Muñoz

Reputation: 645

Based on this answer I managed to achieve it this way but I needed to add a few things like 'output_field' to the subqueries and 'Coalesce' for the annotation, the 'output_field' was required by django, it just doesn't work without it, and 'Coalesce' is required because in case that the results are zero, by default the subquery will return 'null' so what 'Coalesce' does is to retrieve a default value whenever the result is null, which I set to zero in this case:

    childs_base_query = ModelChild.objects.filter(parent=OuterRef('pk'))
    first_type_query = Subquery(childs_base_query
                           .filter(type=1)
                           .values('parent')
                           .annotate(count=Count('pk'))
                           .values('count')
                           , output_field=IntegerField())

    second_type_query = Subquery(childs_base_query
                              .filter(type=2)
                              .values('parent')
                              .annotate(count=Count('pk'))
                              .values('count')
                              , output_field=IntegerField())

    queryset = ModelParent.objects \
        .annotate(first_count=Coalesce(first_type_query, 0)) \
        .annotate(second_count=Coalesce(second_type_query, 0))

I hope it helps someone else.

Upvotes: 2

Related Questions