[Answered ]-Django ORM aggregate over related array field

by

1👍

✅

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')
👤Yevhen Bondar

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)
    ]
👤Most Wanted

Leave a comment