3. 如何在Django ORM中进行AND查询？

_images/usertable.png

如果您使用django.contrib.auth，则会有一个名为auth_user的表。 它将包含username，first_name，last_name等字段。

您经常需要执行AND操作，以查找符合多个条件的查询集。

假设您要查找firstname以'R'开头的用户last_name以'D'开头的用户。

Django提供三种选择。

  • 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>]>

您可以查看生成的查询并验证它们是否完全相同。

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