Annotation with a subquery with multiple result in Django

Question

I use postgresql database in my project and I use below example from django documentation.

from django.db.models import OuterRef, Subquery
newest = Comment.objects.filter(post=OuterRef('pk')).order_by('-created_at')
Post.objects.annotate(newest_commenter_email=Subquery(newest.values('email')[:1]))

but instead of newest commenter email, i need last two commenters emails. i changed [:1] to [:2] but this exception raised: ProgrammingError: more than one row returned by a subquery used as an expression.

score 14 · Answer 1 · answered Jul 31 '20 at 04:36

You'll need to aggregate the subquery results in some way: perhaps by using an ARRAY() construct.

You can create a subclass of Subquery to do this:

class Array(Subquery):
    template = 'ARRAY(%(subquery)s)`
    output_field = ArrayField(base_field=models.TextField())

(You can do a more automatic method of getting the output field, but this should work for you for now: see https://schinckel.net/2019/07/30/subquery-and-subclasses/ for more details).

Then you can use:

posts = Post.objects.annotate(
    newest_commenters=Array(newest.values('email')[:2]),
)

The reason this is happening is because a correlated subquery in postgres may only return one row, with one column. You can use this mechanism to deal with multiple rows, and perhaps use JSONB construction if you need multiple columns.

Annotation with a subquery with multiple result in Django

1 Answers1