[Django]-Annotation with a subquery with multiple result in Django

by

14👍

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.

👤Matthew Schinckel

Leave a comment