Django: Add objects.all.count() to aggregate?

Question

I've been searching how to add X.objects.all().count() result to an existing aggregate logic. I seen that Count() will only work with a foreign key relationship, so how can I add an 'additional field' with the .count() results to the aggregate with only writing it in one block.

This is my custom action (Does not work):

    from django.db.models.functions import Coalesce
    from django.db.models import Min, Avg, Max, Value as Val, IntegerField

    @action(detail=False, methods=['get'])
    def get_stats(self, request, pk=None):
        total_ads = Ad.objects.all().count()
    
        stats = Ad.objects.aggregate(
            price_min = Coalesce(Min('price'), Val(0)), 
            price_avg = Coalesce(Avg('price'), Val(0.0)),
            price_max = Coalesce(Max('price'), Val(0)),
            ads_total = Val(total_ads, output_field=IntegerField()),
        )
    
        return Response(stats)

Error: TypeError: ads_total is not an aggregate expression

willeM_ Van Onsem · Accepted Answer · 2021-08-17 13:46:09Z

2

I seen that Count() will only work with a foreign key relationship, so how can I add an 'additional field' with the .count()

You can also Count the primary key, which will count any non-NULL item, you thus can use:

stats = Ad.objects.aggregate(
    price_min=Coalesce(Min('price'), Val(0)), 
    price_avg=Coalesce(Avg('price'), Val(0.0)),
    price_max=Coalesce(Max('price'), Val(0)),
    ads_total=Count('pk')
)

and you thus can omit the total_ads = Ad.objects.all().count() query, and reduce the number of queries to one.

answered Aug 17, 2021 at 13:46

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Django: Add objects.all.count() to aggregate?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related