format count integer into percentage inside annotate in Djnago rest-CodePudding

I have to send the percentage of counts in the API call in DRF. I have calculated the counts and attaches to the queryset using annotate. But actually I need the percentage rather than the count.

TYPES = ((1,'cold'),
         (2,'humid'),
         (3,'hot'))

from django.db.models import Q,Count

class Destinations(models.Model):

   continent = models.CharField()
   /............/


class Packages(models.Model):

    location = models.Foreignkey(Destination,on_delete=models.CASCADE,related_name='packages')
    place_type = models.CharField(
                            max_length=1,
                            choices=TYPES,
                            default=""

My view:

I have use list function inside modelviewset, something like this:

    Destiantion = Destination.objects.all().annotate(total_packages=Count('packages'),
                                                               
 cold_count=Count('packages',filter=Q(packages__place_type=1)),                                                                   
 humid_count=Count('packages',filter=Q(packages__place_type=2)),
 hot_count =Count('packages',filter=Q(packages__place_type=3)))

Here I get the response as counts of package types in the attributes, but I want is the percentage of package type like 25% by doing cold_count*100/total_packages but can't use this inside annotate.

There is a count() method that might be useful but if I used that I have to make 4 separate queries and might be writing another API just for that. So I have to use annotate. But how??

CodePudding user response：

You should try

from django.db.models.functions import Coalesce

Destination.objects.all().annotate(
    total_packages=Count('packages'),                                                          
    cold_count_percent=Coalesce(100.0 * Count('packages',filter=Q(packages__place_type=1)) / Count('packages'), 0),                                                                   
    humid_count_percent=Coalesce(100.0 * Count('packages',filter=Q(packages__place_type=2)) / Count('packages'), 0),                                    
    hot_count_percent=Coalesce(100.0 * Count('packages',filter=Q(packages__place_type=3)) / Count('packages'), 0),                                    
)

Also if you need to handle the case when Count('packages')=0, you need something like this:

from django.db.models.functions import Coalesce

Destination.objects.all().annotate(
    total_packages=Count('packages'),                                                          
    cold_count_percent=Coalesce(div_zero(100.0 * Count('packages',filter=Q(packages__place_type=1)) / Count('packages'), 'total_packages'), 0),                                                                       
    humid_count_percent=Coalesce(div_zero(100.0 * Count('packages',filter=Q(packages__place_type=2)) / Count('packages'), 'total_packages'), 0),                                    
    hot_count_percent=Coalesce(div_zero(100.0 * Count('packages',filter=Q(packages__place_type=3)) / Count('packages'), 'total_packages'), 0),                                   
)

where div_zero is below:

from django.db.models import Case, Value, FloatField
from django.db.models.expressions import When

INFINITY = 1000000000000 
def div_zero(result, divisor):
    return Case(
        When(**{
            divisor: 0,
            'then': Value(INFINITY, FloatField()),
        }),
        default=result,
        output_field=FloatField(),
    )