How to perform OR condition in django queryset?
PythonSqlDjangoDjango QuerysetDjango OrmPython Problem Overview
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.
Python Solutions
Solution 1 - Python
from django.db.models import Q
User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True))
Solution 2 - Python
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 - Python
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 - Python
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
Solution 5 - Python
In order to add the conditions like "OR" or "AND" as we kind of use in sql queries we have this way as an example
from django.db.models import Q
Poll.objects.get(Q(question__startswith='Who'),Q(pub_date=date(2005, 5, 2)) | Q(pub_date=date(2005, 5, 6)))
this is equivalent to this sql query
SELECT * from polls WHERE question LIKE 'Who%'
AND (pub_date = '2005-05-02' OR pub_date = '2005-05-06')
I hope you are able to understand this properly that the "," is for "AND" operator and "|" is for the "OR" operator used in django.