Question 236593 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to annotate django queryest using subquery where that subquery returns list of ids from related model records

I have one database model, let’s say ModelA:

class ModelA(models.Model):
     field_1 = models.ManyToManyField(
         "core.ModelB", blank=True
     )
     user = models.ForeignKey("core.User", on_delete=models.CASCADE)
     type = models.CharField(choices=ModelATypes.choices)

The field_1 of ModelA is a many to many field that references ModelB.

class ModelB(models.Model):
     uid = models.UUIDField(unique=True, default=uuid.uuid4, editable=False)
     amount = models.IntegerField()

Now, I want to annotate the queryset to show the list of uid’s from field_1 based on some filter like get the list of ModelB uid’s from field_1 for all the ModelA records where user is in list of applicable user ids.

Example:

from django.db.models import Subquery
user_ids = [1, 2, 3]
result = ModelA.objects.filter(type="year").annotate(
     model_b_uids_list=Subquery(
         ModelA.objects.filter(user__id__in=user_ids).values_list("field_1__uid", flat=True)
    )
)

This gives error more than one row returned by a subquery used as an expression. I tried using ArrayAgg function but that does not seems to be working.

I want the annotated field model_b_uids_list on ModelA to have a list of uid’s from the field_1 from multiple applicable records on ModelA.

Can anyone help me understand what’s going wrong here?

Tags:

2

Answers


  1. 0

    Why are you making query so complex? if you just wanted to get the list of ModelB uid’s from field_1 for all the ModelA records where user is in list of applicable user ids, so can’t you do simply like this?

    result = ModelA.objects.filter(type="year", user_id__in=user_ids).values(
            "field_1")
    Login or Signup to reply.
  2. 0

    You can filter the subquery through the ArrayAgg function, as such

    ArrayAgg("other__id", filter=Q(other__name__icontains="x"))

    This is a full example with data that you can play with.

    from django.contrib.postgres.aggregates import ArrayAgg
from django.db.models import *

class Student(Model):
    name = CharField(max_length=100)


class Course(Model):
    title = CharField(max_length=100)
    students = ManyToManyField(Student, related_name="courses")
    def __str__(self):
        return f"Course({self.title!r})"

c = Course.objects.create(title="Agg-ravating Queries")
c.students.add(Student.objects.create(name="Woody"))
c.students.add(Student.objects.create(name="Buzz"))
c.students.add(Student.objects.create(name="Sid"))

qs = Course.objects.filter(title__icontains="queries")
qs = qs.annotate(
    names=ArrayAgg(
        "students__name",
        filter=Q(students__name__icontains="d")
    )
)

for row in qs:
    print(i)
    print(i.names)

>>> Course('Agg-ravating Queries')
>>> ['Woody', 'Sid']
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search