[SOLVED] annotating an object with a list of objects related to it but filtered down

annotating an object with a list of objects related to it but filtered down

I have the following objects (simplified just for this question)

class Object1(models.Model):
  users_assigned_to_object1 = models.ManyToMany(Users, related_name='objects')
  sub_object = models.ForeignKey(SubObject1)

class Users(models.Model):
  first_name = models.CharField(max_length=1000)

class SubObject1(models.Model):
  sub_object_name = models.CharField(max_length=1000)
  
class SubObject2(models.Model):
  sub_object_1 = models.ManyToMany(SubObject1)

class SubObject3(models.Model):
  sub_object_2 = models.ManyToMany(SubObject3)
  role_name = models.CharField(max_length=1000)
  user = models.ForeignKey(Users, related_name='sub_object_relation')

For more complex reasons that do not matter right now, I need to be able to annotate Object1 with the users that are users assigned to object 1 AND where those users have role_name=ADMIN for at least one of the SubObject3 models that are related to Object1.

E.g. I'm trying to get to something like this:

Object1.objects.annotate(admin_users=X)

What goes on X?

I'm on Django 4.2 and using a Postgres DB

I tried using SubQueries but they don't respond with lists (and this can return a list) and tried doing things with Django Conditional expressions (e.g. ArrayAgg, When) but couldn't really get to the right solution on this one.

Solution

Looks like you are looking for ArraySubquery.

from django.contrib.postgres.expressions import ArraySubquery
from django.db.models import F, OuterRef
from django.db.models.functions import JSONObject

users = Users.objects.filter(
    subobject3__role_name='ADMIN', 
    subobject3__sub_object_2__sub_object_1=OuterRef('pk')
).annotate(json=JSONObject(id=F('id'), first_name=F('first_name'))

Object1.objects.annotate(admin_users=ArraySubquery(users.values('json'))