CODEWITHSUNDEEP
sqldjangopostgresqlaggregate-functionsdjango-orm
Milano
Milano

Reputation: 18705

Django/Postgres: Aggregate on RangeField

Is it possible to perform aggregation functions on Django's RangeField?

Let's say we have 3 objects with BigIntegerField price_range.

1st obj: price_range = [10,5000]

2nd obj: price_range = [1,5000]

3rd obj: price_range = [100,9000]

The result of Max and Min aggregation of these three objects would be:

min = 1 and max = 9000

I'm trying to aggregate Max and Min this way:

MyModel.objects.aggregate(Min('price_range'),Max('price_range'),)

Which raises error:

ProgrammingError: function min(int8range) does not exist LINE 1: SELECT MIN("app_mymodel"."price_range") AS "price_range__min" FROM "app...

Upvotes: 5

Views: 532

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476544

You can obtain the range bounds with Upper(..) and Lower(..) so:

from django.db.models.functions import Upper, Lower

MyModel.objects.aggregate(
    Min(Lower('price_range')),
    Max(Upper('price_range'))
)

But note that in case the ranges do not overlap (like for example [0, 20] and [50, 100]), you will still get a range [0, 100].

Apparently modern versions of Django require naming the fields as @EricTheise says:

from django.db.models.functions import Upper, Lower

MyModel.objects.aggregate(
    low=Min(Lower('price_range')),
    high=Max(Upper('price_range'))
)

Upvotes: 6

Related Questions