Django ORM annotate Query

Question

I have a subquery which returns an array. I want to perform certain operations to pick one data out of that array. I am not able to figure out how to do it.

 query_obj = MySecurity.objects.filter(
            valid_data=True
        ).values(
            "id",
            "rate",
        ).annotate(
            rating_val=Subquery(
                MySecurity.object.order_by("-date_of_credit")
                .values("credit_code")
                ),
            )
        )

I want to process my rating_val which will be an array. I tried pass the subquery result to a fun like:

   annotate(
            rating_val=myFun(Subquery(
                MySecurity.object.order_by("-date_of_credit")
                .values("credit_code")
                )),
            )
        )

The above subquery will populate rating_val with ['AA', 'B(CO)']. I want to process this array and find out the lowest rating based on my hierarchy, which I define in const file (rating_hirarchy=['AA', 'B(CO)', '-BB']). According to rating hirarchy 'AA' is greater than 'B(CO) and B(CO) is greater than '-BB'. So now rating_val should have 'B(CO)' as its value But I am not able to access subquery value.

I also tried passing later part of subquery to model manager but again I am not able to access subquery response.

Answer 1

While using subquery, you need to tell Django how the inner query is related to the outer query, and Django provides OuterRef to help filtering the inner query.

Below example is from Django's doc

newest = Comment.objects.filter(post=OuterRef('pk')).order_by('-created_at') Post.objects.annotate(newest_commenter_email=Subquery(newest.values('email')[:1]))

In your code, you did a subquery on self without giving filter conditions, which results in annotating the same value to each row of query. And it's hard to deduce how you really want the subquery related to the outer query.