Django ORM aggregate over related array field

Question

I have two models

class Record(Model):
    scorable_entry = models.ForeignKey('Entry',
                                       null=True,
                                       blank=True,
                                       on_delete=models.CASCADE)

class Entry(Model):
    scores = ArrayField(models.IntegerField(), null=True)

and I need to sort Records based on the sum of scores on a related Entry model.

Unfortunately, this naive code throws an error

records
.annotate(score_rows=Func(F('scorable_entry__scores'), function='unnest'))
.annotate(scores_sum=sum('score_rows'))
.order_by('-scores_sum')

django.db.utils.NotSupportedError: aggregate function calls cannot contain set-returning function calls

I'm using unnest to convert array to rows first (because otherwise sum wouldn't work). Skipping unnesting doesn't work as sum doesn't operate on arrays

django.db.utils.ProgrammingError: function sum(integer[]) does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

What is the proper way to order elements by the sum of a related array using ORM?

Django 3.1, Postgres 12.9

Have you tried: from django.db.models import Sum and Record.objects.annotate(total_entries=Sum('scorable_entry__scores')).order_by('-total_entries') — Ahtisham
– Ahtisham, Commented Jan 11, 2022 at 16:17
@Ahtisham yes, in this case second error pops up like described above — Most Wanted
– Most Wanted, Commented Jan 11, 2022 at 16:24

Yevhen Bondar · Accepted Answer · 2022-01-11 17:32:37Z

2

You can create postgres function to sum up int[] and use it in annotation

create or replace function int_array_sum(arr int[])
returns int
language plpgsql
as
$$
declare
    result integer;
begin
   select sum(a) into result
   from unnest(arr) a;

    return result;
end;
$$;

Here the query

Record.objects
    .annotate(scores_sum=Func(F('scorable_entry__scores'), function='int_array_sum'))
    .order_by('-scores_sum')

answered Jan 11, 2022 at 17:32

Yevhen Bondar

4,7471 gold badge15 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Most Wanted · Accepted Answer · 2023-02-02 15:03:23Z

0

So I've ended up using migration including the custom function from Yevhen's answer

ARRAY_SUM_FUNCTION = """
create or replace function int_array_sum(arr integer[])
returns int
language plpgsql
as
$$
declare
    result integer;
begin
    select sum(a) into result
    from unnest(arr) a;
    return result;
end;
$$;
"""

Note arr integer[] parameter type.

from django.db import migrations


class Migration(migrations.Migration):

    dependencies = [
        ('records', '0003_auto_20151206_1219'),
    ]

    operations = [
        migrations.RunSQL(ARRAY_SUM_FUNCTION)
    ]

edited Feb 2, 2023 at 15:03

answered Jan 12, 2022 at 10:57

Most Wanted

7,2598 gold badges64 silver badges82 bronze badges

Collectives™ on Stack Overflow

Django ORM aggregate over related array field

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related