2. How to do OR queries in Django ORM?¶
If you are using django.contrib.auth
, you will have a table called auth_user
. It will have fields as username
, first_name
, last_name
and more.
A common requirement is performing OR
filtering with two ore more conditions. Say you want find all users with firstname starting with ‘R’ and last_name starting with ‘D’.
Django provides two options.
queryset_1 | queryset_2
filter(Q(<condition_1>)|Q(<condition_2>)
2.1. The query in detail¶
The SQL query for the above condition will look something like
SELECT username, first_name, last_name, email FROM auth_user WHERE first_name LIKE 'R%' OR last_name LIKE 'D%';
Similarly our ORM query would look like
queryset = User.objects.filter(
first_name__startswith='R'
) | User.objects.filter(
last_name__startswith='D'
)
queryset
<QuerySet [<User: Ricky>, <User: Ritesh>, <User: Radha>, <User: Raghu>, <User: rishab>]>
You can also look at the generated query.
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%)'
Alternatively, you can use the Q
objects.
from django.db.models import Q
qs = User.objects.filter(Q(first_name__startswith='R')|Q(last_name__startswith='D'))
If you look at the generated query, the result is exactly the same
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%)'