How to perform OR condition in django queryset?

Question

I want to write a Django query equivalent to this SQL query:

SELECT * from user where income >= 5000 or income is NULL.

How to construct the Django queryset filter?

User.objects.filter(income__gte=5000, income=0)

This doesn't work, because it ANDs the filters. I want to OR the filters to get union of individual querysets.

Solution 1

from django.db.models import Q
User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True))

via Documentation

Solution 2

Because QuerySets implement the Python __or__ operator (|), or union, it just works. As you'd expect, the | binary operator returns a QuerySet so order_by(), .distinct(), and other queryset filters can be tacked on to the end.

combined_queryset = User.objects.filter(income__gte=5000) | User.objects.filter(income__isnull=True)
ordered_queryset = combined_queryset.order_by('-income')

Update 2019-06-20: This is now fully documented in the Django 2.1 QuerySet API reference. More historic discussion can be found in DjangoProject ticket #21333.

Solution 3

Both options are already mentioned in the existing answers:

from django.db.models import Q
q1 = User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True))

and

q2 = User.objects.filter(income__gte=5000) | User.objects.filter(income__isnull=True)

However, there seems to be some confusion regarding which one is to prefer.

The point is that they are identical on the SQL level, so feel free to pick whichever you like!

The Django ORM Cookbook talks in some detail about this, here is the relevant part:

---

queryset = User.objects.filter(
        first_name__startswith='R'
    ) | User.objects.filter(
    last_name__startswith='D'
)

leads to

In [5]: str(queryset.query)
Out[5]: 'SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login",
"auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name",
"auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff",
"auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user"
WHERE ("auth_user"."first_name"::text LIKE R% OR "auth_user"."last_name"::text LIKE D%)'

and

qs = User.objects.filter(Q(first_name__startswith='R') | Q(last_name__startswith='D'))

leads to

In [9]: str(qs.query)
Out[9]: 'SELECT "auth_user"."id", "auth_user"."password", "auth_user"."last_login",
 "auth_user"."is_superuser", "auth_user"."username", "auth_user"."first_name",
  "auth_user"."last_name", "auth_user"."email", "auth_user"."is_staff",
  "auth_user"."is_active", "auth_user"."date_joined" FROM "auth_user"
  WHERE ("auth_user"."first_name"::text LIKE R% OR "auth_user"."last_name"::text LIKE D%)'

source: django-orm-cookbook

---

Solution 4

Just adding this for multiple filters attaching to Q object, if someone might be looking to it. If a Q object is provided, it must precede the definition of any keyword arguments. Otherwise its an invalid query. You should be careful when doing it.

an example would be

from django.db.models import Q
User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True),category='income')

Here the OR condition and a filter with category of income is taken into account

View more solutions

Comments

[0atman]

"undocumented" and "legacy" make me scared. I think it's safer to use the Q object, as detailed in the accepted answer here.

[carruthd]

FYI, order_by() and distinct() can be applied to the piped queryset after they are combined

[hobs]

@carruthd thanks. I confirmed this as well. Will edit

[deadlock]

Can the order_by() be applied to each individual queryset and then combined? So that the order for each condition is still maintained? For example, combined_queryset= User.objects.filter(income__gte=5000).order_by('income') | User.objects.filter(income__lt=5000).order_by('-income') ?

[hobs]

@noahandthewhale I don't know, but that would be very useful. Try it and let us know!

[Eddwin Paz]

It would help if you add a print of object.query so we can relate both ORM and Query output to familiarize with it. BTW great example.

[makeroo]

@Oatman: | operator is documented. See docs.djangoproject.com/en/2.0/ref/models/querysets: "In general, Q() objects make it possible to define and reuse conditions. This permits the construction of complex database queries using | (OR) and & (AND) operators; in particular, it is not otherwise possible to use OR in QuerySets." I did not check documentation for earlier versions but pipe operator works from Django 1.1.4 at least (just tried).

[MHB]

Is it better to use this type of query or perform two separate queries?

[Faiz Hameed]

What if there are some other queries too along with this @lakshman

[Osman Hamashool]

if both queries are same it will return duplicated queries. How to avoid that?

[Osman Hamashool]

if both queries are same it will return duplicated queries. How to avoid that?

[Osman Hamashool]

I just found how to avoid duplicate, after 6 min. Before the query use set() function. like: combined_queryset = set(User.objects.filter(income__gte=5000) | User.objects.filter(income__isnull=True))

[Osman Hamashool]

I just found how to avoid duplicate, after 6 min. Before the query use set() function. like: set(User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True)))

[hobs]

No @OsmanHamashool, I think this is a bad idea. It uses python's builtin set constructor to deduplicate the queryset from your database. Always use .distinct() on your queryset instead. That will run more efficiently within your database (SQL) and not burden your python process. Next time duckup "django queryset unique" to find the .distinct() method in the Django docs.

[Osman Hamashool]

@hobs In my case I got DISTINCT ON fields is not supported by this database backend. So I the python's set works fine for me.

[hobs]

@OsmanHamashool yea you must not have a challenging throughput problem if you are using a database like MySQL that doesn't support DISTINCT ON. Switching to Postgres will give you ~100x speedup on that part of your app, if you ever do need it to run fast.

[Osman Hamashool]

@hobs thanks, there is another down side for using python's set, which is that you can't make ordering to your query sets. I'm planning to switch to postgres now, I will test it again and update the result in here.