条件表达式允许您使用 if
… elif
… else
过滤器、注释、聚合和更新中的逻辑。条件表达式为表的每一行计算一系列条件,并返回匹配的结果表达式。条件表达式也可以像其他表达式一样组合和嵌套 expressions .
我们将在后面的示例中使用以下模型:
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
¶A When()
对象用于封装条件及其结果,以便在条件表达式中使用。使用A When()
对象类似于使用 filter()
方法。可以使用指定条件 field lookups , Q
对象,或 Expression
具有 output_field
那是一个 BooleanField
. 结果是使用 then
关键字。
下面是一些示例:
>>> 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",
... )
请记住,这些值中的每一个都可以是表达式。
备注
自.以来 then
关键字参数是为 When()
,则存在潜在冲突,如果 Model
有一个名为的字段 then
。这可以通过两种方式解决:
>>> When(then__exact=0, then=1)
>>> When(Q(then=0), then=1)
Case
¶A Case()
表达式就像 if
… elif
… else
语句在 Python
.每个 condition
在提供的 When()
对象按顺序进行评估,直到评估为真实值为止。这个 result
匹配的表达式 When()
返回对象。
举个例子:
>>>
>>> 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
返回关键字参数。如果A default
未提供参数, None
使用。
如果我们想要更改之前的查询,以根据 Client
一直与我们在一起,我们可以使用查找来实现:
>>> 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都匹配。这就像一个 if
… elif
… else
语句在 Python
.
Case()
也适用于 filter()
第。条。例如,查找一个多月前注册的黄金客户和一年多前注册的白金客户:
>>> 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')]>
条件表达式可用于批注、聚合、筛选器、查找和更新。也可以与其他表达式组合。这允许您进行功能强大的条件查询。
假设我们想要更改 account_type
让我们的客户匹配他们的注册日期。我们可以使用条件表达式和 update()
方法:
>>> 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')]>
如果我们想要找出每个客户端有多少个客户端,该怎么办 account_type
?我们可以使用 filter
的论点 aggregate functions 要实现此目标,请执行以下操作:
>>> # 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
可能表现更好。
当条件表达式返回布尔值时,可以直接在筛选器中使用它。这意味着它不会被添加到 SELECT
列,但您仍然可以使用它来筛选结果:
>>> 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
)
12月 18, 2023