[Django]-Django ORM: Filtering by array contains OuterRef within a subquery yields zero results

by

6👍

✅

I don’t have your models or any value so I can’t check if what I’m saying is working.

I’ll assume you’re using Postgres and Django 2.2

The main problem is django has troubles with casting [Anything] when Anything is not a simple string. It does not work with OuterRef, nor F expressions (It’s what I used to reproduce with a simpler query)

So what I would do is to use a function to cast it as an array directly in Postgres: 

from django.db.models import Func
Purpose.objects.annotate(
    conversation_count=SubqueryCount(
        Conversation.objects.filter(
            goal_slugs__contains=Func(
                OuterRef("slug"),
                function="ARRAY",
                template="%(function)s[%(expressions)s]",
            )
        ).values("id")
    )
)

Alternatively, since you’re just checking that a value belongs to an array, I would use ANY in Postgres. To do that, you can define a custom lookup:

from django.db.models import Lookup
from django.db.models.fields import Field


class EqualAny(Lookup):
    lookup_name = "any"

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params

        # Notice I reversed right and left here for your example
        return "%s = ANY (%s)" % (rhs, lhs), params




# We need to register the lookup here to make sure it happens during the app setup
Field.register_lookup(EqualAny)

Purpose.objects.annotate(
    conversation_count=SubqueryCount(
        Conversation.objects.filter(goal_slugs__any=OuterRef("slug")).values("id")
    )
)

If you provide models, I could inspect the generated SQL to check it worked

👤Lotram

Leave a comment