Django Aggregate Min Max Dynamic Ranges-CodePudding

I have following model:

class Claim:
      amount = models.PositiveIntegerField()

I am trying to create API that dynamically sends response in such way that amount ranges are dynamic.

For example my minimum Claim amount is 100 and max is 1000 I wanted to show JSON in this way:

{
    "100-150": 2,
    "150-250": 3,
    "250-400": 1,
    "400-500": 5,
    "above_500": 12
}

I tried doing this way assuming my data range is between 1 and 2000 but this becomes of no use if my minimum amount lies in between 10000 and 100000.

d = Claim.objects.aggregate(upto_500=Count('pk', filter=Q(amount__lte=500)),
                                    above_500__below_1000=Count('pk', filter=Q(amount__range=[501, 999])),
                                    above_1000__below_2000=Count('pk', filter=Q(amount__range=[1000, 2000])),
                                    above_2000=Count('pk', filter=Q(amount__gte=2000))
                                    )

Any idea how we can make dynamic way of getting amount ranges and throwing it to frontend?

CodePudding user response：

from django.db.models import Q

upto_500=Count('pk', filter=Q(amount__lte=500))
                            

above_500__below_1000=Count('pk', filter=Q(amount__range=(501, 999)))
                            

above_1000__below_2000=Count('pk', filter=Q(amount__range=(1000, 2000)))
                            

above_2000=Count('pk', filter=Q(amount__gte=2000))

claim = Claim.objects.annotate(upto_500=upto_500).annotate(above_500__below_1000=above_500__below_1000).annotate(above_1000__below_2000=above_1000__below_2000).annotate(above_2000=above_2000)

print(claim[0].upto_500)

this is a count of less than 500 and so on.

now you can easily create JSON from it.

CodePudding user response：

I think this is what are you looking for:

from django.db.models import Max, Min, Count, Q

claim_min_max = Claim.objects.aggregate(Min("amount"), Max("amount"))

amount_min = claim_min_max["amount__min"]
amount_max = claim_min_max["amount__max"]
step = 100

elements = range(amount_min, amount_max, step)
pairs = []
for i in range(len(elements)):
    try:
        pairs.append(elements[i], elements[i   1])
    except IndexError:
        break

aggregate_pairs = {
    f"from_{_from}_to_{_to}": Count("pk", filter=Q(amount__range=[_from, _to]))
    for _from, _to in pairs
}

queryset = Claim.objects.aggregate(**aggregate_pairs)

A dynamic way to count elements in batches