CODEWITHSUNDEEP
djangodjango-modelsdjango-orm
Robert Townley
Robert Townley

Reputation: 3574

Inconsistent Django ORM Query Results With "Q" vs "__in"

I'm trying to find all instances of one of my models that has an attribute set either to None or to an empty string. I think I'm missing something about the ORM's __in query.

TextBlock.objects.filter(content='').count()
>185
TextBlock.objects.filter(content=None).count()
>235
TextBlock.objects.filter(content__in=['', None]).count()
>185
TextBlock.objects.filter(Q(content='')|Q(content=None)).count()
>420
TextBlock.objects.filter(content__in=[None, '']).count()
>185

When I do this with an IntegerFIeld and not using None or empty strings, I get:

TextBlock.objects.filter(order=1).count()
> 575046
TextBlock.objects.filter(order=2).count()
> 11946
TextBlock.objects.filter(order__in=[1,2]).count()
> 586992

This is what I'd expect. Was I wrong to expect TextBlock.objects.filter(content__in=['', None]).count() to return 420 results? If so, why?

The Q query does what I want, so I'll end up going with that, but I'm still curious about why that third query didn't do what I wanted.

Thanks!

EDIT: Not sure if it makes a difference, but the content field is a models.TextField and the order field is a models.IntegerField

Upvotes: 0

Views: 126

Answers (3)

knbk
knbk

Reputation: 53699

Note that NULL == NULL is actually false in SQL, and you need to use IS NULL to check if a value is null. Django is smart enough to convert Q(content=None) to an IS NULL statement, that's why TextBlock.objects.filter(Q(content='')|Q(content=None)).count() returns the results you'd expect.

An IN statement can only compare a value against its elements, it can't do an IS NULL check. To check against null values you need to explicitly add content=None or the equivalent content__isnull=True.

The query using __in should work as expected for any list of values that doesn't include NULL.

Upvotes: 3

AKS
AKS

Reputation: 19841

You check for None values using isnull field lookup:

TextBlock.objects.filter(Q(content='')|Q(content__isnull=True)).count()

Upvotes: 0

Dariosky
Dariosky

Reputation: 531

For checking if content is None you shouldn't use the comparison. You have to use content__isnull in the ORM (and you can combine queries with the Q object like you're doing)

Upvotes: 0

Related Questions