Django queryset matching null value with null OuterRef

user_9438366 0
Viewed 544

In Django, I'm trying to filter Foo objects by whether a Bar object exists with the same values. The code I have below works fine for non-null values. But I also want it to return true if Foo.baz and Bar.baz are both null.

Foo.objects.filter(Exists(Bar.objects.filter(baz=OuterRef('baz'), qux=OuterRef('qux'))

I know NULL is not equal to NULL in SQL so have tried various formulations like this:

baz__isnull=...

But I'm not getting very far. Is there a way to achieve this?

orm django
1 Answers

You can annotate the value of OuterRef('baz') and use that in your filters. Since you need to perform OR conditions you would need to use Q objects [Django docs]. Also it looks like filtering on the annotated OuterRef will not work because of this bug having Ticket #31714, but we can get around that by using an ExpressionWrapper to manually specify the output_field. Hence if baz is an IntegerField your query would look like:

from django.db.models import ExpressionWrapper, F, IntegerField, Q

q_object = Q(baz=F('outer_baz')) | (Q(baz__isnull=True) & Q(outer_baz__isnull=True))

Foo.objects.filter(
    Exists(
        Bar.objects.annotate(
            outer_baz=ExpressionWrapper(
                OuterRef('baz'),
                output_field=IntegerField()
            ) # Workaround to overcome bug
        ).filter(
            q_object,
            qux=OuterRef('qux')
        )
    )
)
user_149918640
Related