CODEWITHSUNDEEP
djangopostgresql
AlxVallejo
AlxVallejo

Reputation: 3219

Django: Return array in subquery

I have a model Application that has many Judge objects assigned to it via ForeignKey. Each Judge will grade the Application. I want to return a list of Applications in a QuerySet along with an array of each of the score values. Here's how I"m trying to accomplish that:

total_scores = Subquery(
    Judge.objects
    .filter(
        application=OuterRef('pk')
    )
    .values_list(
        'total_score',
        flat=True
    )
)

applications = Application.objects \
    .annotate(
        score_array=total_scores
    )

But I get this error:

more than one row returned by a subquery used as an expression

The values_list should return an array of values, so i'm not sure why multiple rows are being returned from my subquery. Any pointers? Thanks.

Upvotes: 10

Views: 8921

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476557

First of all, arrays are typically not a well implemented structure in most (all) databases, so I would advice to keep away from it as much as possible. Especially since it structures row-wise data into a single column, which makes it harder to perform JOINs, etc.

But if you really want this, you can use the ArrayAgg aggregate function. This is however a PostgreSQL specific function, thus you lose some freedom to pick another database system.

You can then probably obtain such result with:

from django.contrib.postgres.aggregates import ArrayAgg

Application.objects.annotate(
    score_array=ArrayAgg('judge__total_score')
)

So this does not require a SubQuery.

Upvotes: 13

Related Questions