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 `AND`s the filters. I want to `OR` the filters to get union of individual querysets.

Answer 1

The `django.db.models` package provides a `Q` object that you can use to compose more filtering statements. Instead of using with `.filter()`, you use it with `.get()`, combining multiple OR clauses with a vertical pipe `|`. For example:

from django.db.models import Q

query_set = User.objects.get(

Q(income__gte=5000) | Q(income=0)

)

This would roughly translate into:

SELECT * from users

WHERE income >= 5000 OR income = 0

You can combine multiple comma-separated `Q` objects to `.get()`, which will act like parenthesized `AND` clauses.

User.objects.get(

Q(income__gte=5000) | Q(income=0),

Q(email__endswith='gmail.com') | Q(email__endswith='yahoo.com')

)

which would be like

SELECT * FROM users

WHERE (income >= 5000 OR income = 0)

AND (email LIKE '%gmail.com' OR email LIKE '%yahoo.com')

If you combine `Q` clauses with keyword arguments, just make sure that the `Q` argument comes first:

User.objects.get(

Q(income__gte=5000) | Q(income=0),

email__endswith='gmail.com'

)

For more information, check out the Django documentation page: https://docs.djangoproject.com/en/2.2/topics/db/queries/#complex-lookups-with-q-objects