• ru
  • Language: en
  • Documentation version: 2.0

3. How to do AND queries in Django ORM?

../_images/usertable.png

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.

You would frequently need to want to perform AND operation, to find querysets which match multiple criteria.

Say you want to find users with firstname starting with ‘R’ AND last_name starting with ‘D’.

Django provides three options.

  • filter(<condition_1>, <condition_2>)

  • queryset_1 & queryset_2

  • filter(Q(<condition_1>) & Q(<condition_2>))

3.1. The query in detail

Our 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%' AND last_name LIKE 'D%';
../_images/sqluser_result2.png

The default way to combine multiple conditions in filter is AND, so you can just do.

queryset_1 = User.objects.filter(
    first_name__startswith='R',
    last_name__startswith='D'
)

Alternatively, you can explicitly use the & operator on querysets.

queryset_2 = User.objects.filter(
    first_name__startswith='R'
) & User.objects.filter(
    last_name__startswith='D'
)

For complete customisability, you can use the Q objects.

queryset_3 = User.objects.filter(
    Q(first_name__startswith='R') &
    Q(last_name__startswith='D')
)


queryset_1
<QuerySet [<User: Ricky>, <User: Ritesh>, <User: rishab>]>

You can look at the generated query and verify that they are all same.

In [10]: str(queryset_2.query)
Out[10]: '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% AND "auth_user"."last_name"::text LIKE D%)'

In [11]: str(queryset_1.query) == str(queryset_2.query) == str(queryset_3.query)
Out[11]: True
2. How to do OR queries in Django ORM?
4. How to do a NOT query in Django queryset?