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.
from django.db.models import Q
User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True))
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.
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.
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.
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
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
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.
Success story sharing
set(User.objects.filter(Q(income__gte=5000) | Q(income__isnull=True)))