CODEWITHSUNDEEP
djangodjango-ormdjango-postgresqldjango-annotatedjango-subquery
AlexMercer
AlexMercer

Reputation: 311

Annotation with a subquery with multiple result in Django

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.

Upvotes: 3

Views: 4815

Answers (1)

Matthew Schinckel
Matthew Schinckel

Reputation: 35629

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.

Upvotes: 14

Related Questions