format count integer into percentage inside annotate in Djnago rest

Question

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??

Oleksii Tambovtsev · Accepted Answer · 2021-12-23T18:53:18.700

1

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(),
    )

edited Dec 23 '21 at 18:53

answered Dec 23 '21 at 18:45

Oleksii Tambovtsev

2,666
1
3
21

can you tell me how to annotate like this in for single id like `article = Articles.objects.get(id=id)`...it worked for `objects.all()`only. – Reactoo Dec 24 '21 at 05:13
ok had to use get after annotate..thanks! – Reactoo Dec 24 '21 at 05:15
ok last question, how to add % sign at the end result?? – Reactoo Dec 24 '21 at 05:19
1

In general, you should do this action at the frontend level of your application (if you have one, of course). Variables `cold_count_percent`, `humid_count_percent` and `hot_count_percent` should be float field in your API response. – Oleksii Tambovtsev Dec 24 '21 at 11:08
@Reactoo Is there anything else I can help you with? If not, could you please mark the answer as accepted? – Oleksii Tambovtsev Dec 27 '21 at 12:06
Hello @Oleksii, one final help. I got an issue saying ` FieldError: Expression contains mixed types: FloatField, IntegerField. You must set output_field.` where to specify output field? – Reactoo Dec 28 '21 at 04:32
there is an issue. one final issue..i cant use 1000000 because it will display as 100000 percent if I have 0 packages. if I put None it will give you must set output field. – Reactoo Dec 28 '21 at 10:14
You should use 0 instead 1000000000 in your case, because if packages’ count is 0 then cold_count_percent is 0 too. – Oleksii Tambovtsev Dec 28 '21 at 10:33
i mean total packages count are 0, meaning all other packages count should be zero for that destination. – Reactoo Dec 28 '21 at 10:34
Yes, I understood. So if total packages count is 0, then you should use value 0 instead 1000000000 and then all other counts will be 0 too. – Oleksii Tambovtsev Dec 28 '21 at 10:55
however, can you explain me in short, it doenst make sense if the divisior is 0, making the infinity value 0 again, wont that makes the divisor 0 again? – Reactoo Dec 28 '21 at 11:02
1

INIFINITY is just the value that will be used when the divisor is 0, it does not change the divisor value. So, in your case, if `total_package_count = 0` then `cold_count_percent = INFINITY`, otherwise `cold_count_percent = 100.0 * Count('packages',filter=Q(packages__place_type=1) / total_package_count`. – Oleksii Tambovtsev Dec 28 '21 at 11:32

format count integer into percentage inside annotate in Djnago rest

1 Answers1