条件表达式让你可以在过滤器、注解、聚合和更新中使用 if
... elif
... else
逻辑。条件表达式对表的每条记录执行一系列条件,并返回匹配的结果表达式。条件表达式也可以像其他 表达式 一样进行组合和嵌套。
在后续的例子中,我们将使用以下模型:
from django.db import models
class Client(models.Model):
REGULAR = "R"
GOLD = "G"
PLATINUM = "P"
ACCOUNT_TYPE_CHOICES = [
(REGULAR, "Regular"),
(GOLD, "Gold"),
(PLATINUM, "Platinum"),
]
name = models.CharField(max_length=50)
registered_on = models.DateField()
account_type = models.CharField(
max_length=1,
choices=ACCOUNT_TYPE_CHOICES,
default=REGULAR,
)
When
¶When
(condition=None, then=None, **lookups)¶When()
对象用于封装一个条件及其结果,以便在条件表达式中使用。使用 When()
对象类似于使用 filter()
方法。可以使用 字段查找、 Q
对象或 Expression
对象来指定条件,这些对象的 output_field 是 BooleanField
。结果是用 then
关键字提供的。
Some examples:
>>> from django.db.models import F, Q, When
>>> # String arguments refer to fields; the following two examples are equivalent:
>>> When(account_type=Client.GOLD, then="name")
>>> When(account_type=Client.GOLD, then=F("name"))
>>> # You can use field lookups in the condition
>>> from datetime import date
>>> When(
... registered_on__gt=date(2014, 1, 1),
... registered_on__lt=date(2015, 1, 1),
... then="account_type",
... )
>>> # Complex conditions can be created using Q objects
>>> When(Q(name__startswith="John") | Q(name__startswith="Paul"), then="name")
>>> # Condition can be created using boolean expressions.
>>> from django.db.models import Exists, OuterRef
>>> non_unique_account_type = (
... Client.objects.filter(
... account_type=OuterRef("account_type"),
... )
... .exclude(pk=OuterRef("pk"))
... .values("pk")
... )
>>> When(Exists(non_unique_account_type), then=Value("non unique"))
>>> # Condition can be created using lookup expressions.
>>> from django.db.models.lookups import GreaterThan, LessThan
>>> When(
... GreaterThan(F("registered_on"), date(2014, 1, 1))
... & LessThan(F("registered_on"), date(2015, 1, 1)),
... then="account_type",
... )
请记住,每个值都可以是一个表达式。
备注
Since the then
keyword argument is reserved for the result of the
When()
, there is a potential conflict if a
Model
has a field named then
. This can be
resolved in two ways:
>>> When(then__exact=0, then=1)
>>> When(Q(then=0), then=1)
Case
¶Case
(*cases, **extra)¶Case()
表达式就像 Python
中的 if
... elif
... else
语句。在提供的 When()
对象中的每个 condition
按顺序执行,直到执行出一个对的值。从匹配的 When()
对象中返回 result
表达式。
An example:
>>>
>>> from datetime import date, timedelta
>>> from django.db.models import Case, Value, When
>>> Client.objects.create(
... name="Jane Doe",
... account_type=Client.REGULAR,
... registered_on=date.today() - timedelta(days=36),
... )
>>> Client.objects.create(
... name="James Smith",
... account_type=Client.GOLD,
... registered_on=date.today() - timedelta(days=5),
... )
>>> Client.objects.create(
... name="Jack Black",
... account_type=Client.PLATINUM,
... registered_on=date.today() - timedelta(days=10 * 365),
... )
>>> # Get the discount for each Client based on the account type
>>> Client.objects.annotate(
... discount=Case(
... When(account_type=Client.GOLD, then=Value("5%")),
... When(account_type=Client.PLATINUM, then=Value("10%")),
... default=Value("0%"),
... ),
... ).values_list("name", "discount")
<QuerySet [('Jane Doe', '0%'), ('James Smith', '5%'), ('Jack Black', '10%')]>
Case()
接受任意数量的 When()
对象作为单个参数。其他选项是通过关键字参数提供的。如果没有一个条件的值是 TRUE
,那么将返回用 default
关键字参数给出的表达式。如果没有提供 default
参数,则使用 None
。
If we wanted to change our previous query to get the discount based on how long
the Client
has been with us, we could do so using lookups:
>>> a_month_ago = date.today() - timedelta(days=30)
>>> a_year_ago = date.today() - timedelta(days=365)
>>> # Get the discount for each Client based on the registration date
>>> Client.objects.annotate(
... discount=Case(
... When(registered_on__lte=a_year_ago, then=Value("10%")),
... When(registered_on__lte=a_month_ago, then=Value("5%")),
... default=Value("0%"),
... )
... ).values_list("name", "discount")
<QuerySet [('Jane Doe', '5%'), ('James Smith', '0%'), ('Jack Black', '10%')]>
备注
请记住,条件是按顺序计算的,所以在上面的例子中,尽管第二个条件同时符合 Jane Doe 和 Jack Black,我们还是得到了正确的结果。这就像在 Python
中的 if
... elif
... else
语句一样。
Case()
also works in a filter()
clause. For example, to find gold
clients that registered more than a month ago and platinum clients that
registered more than a year ago:
>>> a_month_ago = date.today() - timedelta(days=30)
>>> a_year_ago = date.today() - timedelta(days=365)
>>> Client.objects.filter(
... registered_on__lte=Case(
... When(account_type=Client.GOLD, then=a_month_ago),
... When(account_type=Client.PLATINUM, then=a_year_ago),
... ),
... ).values_list("name", "account_type")
<QuerySet [('Jack Black', 'P')]>
条件表达式可用于注解、聚合、过滤器、查找和更新中。它们还可以与其他表达式组合和嵌套。这使你可以进行强大的条件查询。
Let's say we want to change the account_type
for our clients to match
their registration dates. We can do this using a conditional expression and the
update()
method:
>>> a_month_ago = date.today() - timedelta(days=30)
>>> a_year_ago = date.today() - timedelta(days=365)
>>> # Update the account_type for each Client from the registration date
>>> Client.objects.update(
... account_type=Case(
... When(registered_on__lte=a_year_ago, then=Value(Client.PLATINUM)),
... When(registered_on__lte=a_month_ago, then=Value(Client.GOLD)),
... default=Value(Client.REGULAR),
... ),
... )
>>> Client.objects.values_list("name", "account_type")
<QuerySet [('Jane Doe', 'G'), ('James Smith', 'R'), ('Jack Black', 'P')]>
What if we want to find out how many clients there are for each
account_type
? We can use the filter
argument of aggregate
functions to achieve this:
>>> # Create some more Clients first so we can have something to count
>>> Client.objects.create(
... name="Jean Grey", account_type=Client.REGULAR, registered_on=date.today()
... )
>>> Client.objects.create(
... name="James Bond", account_type=Client.PLATINUM, registered_on=date.today()
... )
>>> Client.objects.create(
... name="Jane Porter", account_type=Client.PLATINUM, registered_on=date.today()
... )
>>> # Get counts for each value of account_type
>>> from django.db.models import Count
>>> Client.objects.aggregate(
... regular=Count("pk", filter=Q(account_type=Client.REGULAR)),
... gold=Count("pk", filter=Q(account_type=Client.GOLD)),
... platinum=Count("pk", filter=Q(account_type=Client.PLATINUM)),
... )
{'regular': 2, 'gold': 1, 'platinum': 3}
在支持 SQL 2003 FILTER WHERE
语法的数据库上,这个聚合产生一个查询。
SELECT count('id') FILTER (WHERE account_type=1) as regular,
count('id') FILTER (WHERE account_type=2) as gold,
count('id') FILTER (WHERE account_type=3) as platinum
FROM clients;
在其他数据库中,这是用 CASE
语句模拟的:
SELECT count(CASE WHEN account_type=1 THEN id ELSE null) as regular,
count(CASE WHEN account_type=2 THEN id ELSE null) as gold,
count(CASE WHEN account_type=3 THEN id ELSE null) as platinum
FROM clients;
这两条 SQL 语句在功能上是等同的,但更明确的 FILTER
可能表现得更好。
When a conditional expression returns a boolean value, it is possible to use it
directly in filters. This means that it will not be added to the SELECT
columns, but you can still use it to filter results:
>>> non_unique_account_type = (
... Client.objects.filter(
... account_type=OuterRef("account_type"),
... )
... .exclude(pk=OuterRef("pk"))
... .values("pk")
... )
>>> Client.objects.filter(~Exists(non_unique_account_type))
用 SQL 术语来说,它的值是:
SELECT ...
FROM client c0
WHERE NOT EXISTS (
SELECT c1.id
FROM client c1
WHERE c1.account_type = c0.account_type AND NOT c1.id = c0.id
)
9月 22, 2023