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

user_1834570 0
Viewed 997

Here's an isolated ORM query:

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

Where SubqueryCount is:

from django.db.models import IntegerField
from django.db.models.expressions import Subquery


class SubqueryCount(Subquery):
    template = "(SELECT count(*) FROM (%(subquery)s) _count)"
    output_field = IntegerField()

When that query is run, the following sql is used (via djdt explain):

SELECT
    "taxonomy_purpose"."id",
    "taxonomy_purpose"."slug",
    (
        SELECT count(*)
        FROM (
            SELECT U0."id"
            FROM "conversations_conversation" U0
            WHERE U0."goal_slugs" @> ARRAY['ResolvedOuterRef(slug)']::varchar(100)[]
        ) _count
    ) AS "conversation_count"
FROM "taxonomy_purpose"

Note the ResolvedOuterRef(slug) injected into the ARRAY lookup as a string. Am I doing something wrong, or should I report this as a bug? If this is a bug, is there a known workaround (maybe creating a custom OuterRef class)?

django-orm django
1 Answers

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

user_56599660
Related