Return multiple values in Subquery in Django ORM

user_11251373 0
Viewed 4156

Question is regarding Subquery and ArrayAgg in Django ORM.

For example I have 2 models without any relationship one to another:


class Example1(models.Model):
    ident = Integerfield()

class Example2(models.Model):
    ident = IntegerField()
    email = EmailField()

There is no connection between 2 models like FK, M2M, O2O, but field ident might be same integer in both models (which is a connection in a way)and in general for 1 instance of Example1 there are multiple instances of Example2 with same ident.

I want to make a subquery or arrayagg (db Postgres) or any way outside RAWSQL to make an annotation like this:

Example1.objects.annotate(
cls2=Subquery(
Example2.objects.filter(
ident=OuterRef(‘ident’
).values_list(‘email’, flat=True).

#or

Example1.objects.annotate(
cls2=StringAgg(
something here???, 
delimeter=’, ‘,
 distinct=True,)

Sure that this does not work as Subquery returns multiple rows and it seems like it impossible to use StringAgg as we dont have any connections between models(nothing to put inside StringAgg).

Any ideas how to annotate Example1 with emails from Example2 in one queryset?

This will be used in CASE expression.

Thanks...

django-orm django
1 Answers

For MySQL backend, you can use GroupConcat of django-mysql, or take a look on the post to make a aggregate function youself:

from django_mysql.models import GroupConcat
Example1.objects.annotate(
    cls2=Subquery(
        Example2.objects.filter(ident=OuterRef('ident')).values('ident')\
        .annotate(emails=GroupConcat('email')).values('emails')
    )
)

For PostgreSQL backend, you can use ArrayAgg or StringAgg:

from django.contrib.postgres.aggregates import ArrayAgg, StringAgg
Example1.objects.annotate(
    cls2=Subquery(
        Example2.objects.filter(ident=OuterRef('ident')).values('ident')\
        .annotate(emails=ArrayAgg('email')).values('emails')
    )
)
# or
Example1.objects.annotate(
    cls2=Subquery(
        Example2.objects.filter(ident=OuterRef('ident')).values('ident')\
        .annotate(emails=StringAgg('email', ',')).values('emails')
    )
)
user_134374510
Related