5

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

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
Milano
  • 18,048
  • 37
  • 153
  • 353

1 Answers1

6

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'))
)
dfrankow
  • 20,191
  • 41
  • 152
  • 214
Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • Yes, that is exactly what I want. It's for filtering purposes. Thanks – Milano Jul 09 '18 at 08:14
  • 2
    Gratefully encountering this answer a couple of years later, Django (3.0.6) gives me a `TypeError: Complex expressions require an alias`. Guided by [this answer](https://stackoverflow.com/a/32407777/1332624) I needed to do the equivalent of `MyModel.objects.aggregate(lower=Min(Lower('price_range')), upper=Max(Upper('price_range')))` – Eric Theise Jul 18 '20 at 16:01