Django queryset filter - Q() | VS __in

django filter django-queryset django-orm django-q
Ross Lote picture Ross Lote · Oct 2, 2015 · Viewed 11.1k times · Source

What is the difference between

queryset.filter(Q(foo='bar') | Q(foo='baz'))

and

queryset.filter(foo__in=['bar', 'baz'])

I'm finding that sometimes they produce different results and I can't figure out why.

I'm getting different results with these queries:

In [8]: Profile.objects.filter(image="").count()
Out[8]: 7173

In [9]: Profile.objects.filter(image=None).count()
Out[9]: 25946

In [10]: Profile.objects.filter(image__in=["", None]).count()
Out[10]: 7173

In [11]: Profile.objects.filter(Q(image="") | Q(image=None)).count()
Out[11]: 33119

I'm using PostgreSQL as my database engine.

Answer

GwynBleidD picture GwynBleidD · Oct 2, 2015

First will generate query:

SELECT .... FROM ... WHERE (FOO = 'bar' OR FOO = 'baz');

second will generate query:

SELECT .... FROM ... WHERE (FOO IN ('bar', 'baz'));

Both queries should compute same results, but there may be some performance differences, depending on database backend. Generally, using in should be faster.

Related questions

Filtering for empty or NULL names in a queryset

I have first_name, last_name & alias (optional) which I need to search for. So, I need a query to give me all the names that have an alias set. Only if I could do: Name.objects.filter(alias!="") …

Read More
How can I filter a date of a DateTimeField in Django?

I am trying to filter a DateTimeField comparing with a date. I mean: MyObject.objects.filter(datetime_attr=datetime.date(2009,8,22)) I get an empty queryset list as an answer because (I think) I am not considering time, but I want "…

Read More
Django filter queryset __in for *every* item in list

Let's say I have the following models class Photo(models.Model): tags = models.ManyToManyField(Tag) class Tag(models.Model): name = models.CharField(max_length=50) In a view I have a list with active filters called categories. I want to filter …

Read More