Django 1.11 order results issue - annotating count returns wrong value

Question

I am using django 1.11:

• I have a model definition that calculates the number of false values on a foreign key attribute, like so:

Model:

class Model(models.Model):
    .
    . 
    . 

    def count_total(self):
        return self.anothermodel_set.filter(val=False).count()

View:

class ModelViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Model.objects.all()
    serializer_class = ModelSerializerClass
    permissions = AuthenticatedReadOnly
    pagination_class = StandardResultsSetPagination

    def list(self, request):
        queryset = self.get_queryset()

        # Other annotations...

        # Attempt 1: returns wrong count
        queryset = queryset.annotate(
            a_count=Count(
                Case(
                    When(anothermodel__val=False, then=1), 
                    default=0, 
                    output_field=IntegerField()
                )
            )
        )

        # Attempt 2: returns wrong count, same as attempt 1
        queryset = queryset.annotate(
            b_count=Count(Q(anothermodel__val=False))
        )

        # Ideally I want to do 
        queryset = queryset.order_by('count_total')

When I do the ordering by count_total it I'll give

FieldError at /api/endpoint/ Cannot resolve keyword 'count_total' into field.

Because count_total is a model definition.

Serializer:

In my serializer I've modified the to_represantation definition to debug:

def to_representation(self, instance):
    return {'id': instance.pk, 'a_count': instance.a_count, 'b_count' : instance.b_count, 'correct_count': instance.count_total()}

Otherwise in my serializer I have:

class Meta:
    model = Model
    fields = ('id', 'title', 'bunch-of-other-stuff', 'count_total')

The instance.count_total() returns the correct result but I cannot simply use it as queryset.order_by('count_total'). I need to annotate the correct value in order to sort the results and to also avoid n+1 query problems.

Answer 1

I'm not sure if this is your problem or not but the model definition appears unused because you're not returning a value, just doing the calculation. It should be:

def count_total(self):
        return self.anothermodel_set.filter(val=False).count()

I'm also confused as to why you would have a calculation occurring in both the model and your view. Either use the model definition or annotate your queryset but you shouldn't need to do both. If you go the model definition route your view should look something like this:

class ModelViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Model.objects.all()
    serializer_class = ModelSerializer
    permissions = AuthenticatedReadOnly
    pagination_class = StandardResultsSetPagination

    def list(self, request):

        queryset = self.queryset.order_by('count_total')
        serializer = self.get_serializer(queryset, many=True)

        return Response(serializer.data)

If you use the model definition you'll need to set something like a read_only attribute in your serializer:

count_total = serializers.ReadOnlyField(allow_null=True)

class Meta:
    model = Model
    fields = ('id', 'title', 'bunch-of-other-stuff', 'count_total')

If you're still getting that "unable to resolve count total into field" error throw a print statement into the view set before you run your serializer like this:

print(str(queryset))

The problem might be on how you're filtering the model definition.

Answer 2

Almost 3 years later; but it may save some time for others who encounter this.

# Attempt 2: returns wrong count, same as attempt 1
queryset = queryset.annotate(b_count=Count(Q(anothermodel__val=False)))

The issue has to do with the fact that multiple annotations were combined. The missing part from the above was to specify that we only want to count distinct=True values to avoid this issue. Like so:

# Success
queryset = queryset.annotate(
    b_count=Count(
        Q(anothermodel__val=False), distinct=True
    )
)