下面记录的类为用户提供了一种使用底层数据库提供的函数作为Django中的注释、聚合或过滤器的方法。函数也是表达式,因此它们可以与其他表达式(如聚合函数)一起使用和组合。

以下的例子将使用下面的模型:

1
2
3
4
5
6
class Author(models.Model):

name = models.CharField(max_length=50)
age = models.PositiveIntegerField(null=True, blank=True)
alias = models.CharField(max_length=50, null=True, blank=True)
goes_by = models.CharField(max_length=50, null=True, blank=True)

Cast

返回一个指定类型的结果

1
2
3
4
5
6
from django.db.models import FloatField
from django.db.models.functions import Cast

Author.objects.create(name="tony", age=25)
author = Author.objects.annotate(age_as_float=Cast("age", output_field=FloatField())).get()
print(author.age_as_float) # 25.0

Coalesce

接受至少包含两个字段名或表达式,并返回第一个非空值(请注意,空字符串不被视为空值),每个参数的类型必须相似

1
2
3
4
5
6
7
8
9
10
from django.db.models.functions import Coalesce
from django.db.models import Sum

Author.objects.create(name='Margaret Smith', goes_by='Maggie')
author = Author.objects.annotate(screen_name=Coalesce('alias', 'goes_by', 'name')).get()
print(author.screen_name) # Maggie

aggregated = Author.objects.aggregate(combined_age=Coalesce(Sum('age'), 0),combined_age_default=Sum('age')).get()
print(aggregated["combined_age"]) # 0
print(aggregated["combined_age_default"]) # None

Greatest

接受至少两个字段名称或表达式,并返回最大值,每个参数必须具有相似的类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
class Blog(models.Model):
body = models.TextField()
modified = models.DateTimeField()

class Comment(models.Model):
body = models.TextField()
modified = models.DateTimeField()
blog = models.ForeignKey(Blog, on_delete=models.CASCADE)

from django.db.models.functions import Greatest

blog = Blog.objects.create(body='Greatest is the best.', modified="2020-01-06 12:30:00")
comment = Comment.objects.create(body='No, Least is better.', blog=blog, modified="2020-01-07 12:30:00")
comments = Comment.objects.annotate(last_updated=Greatest('modified', 'blog__modified'))
annotated_comment = comments.get()
print(annotated_comment.last_updated) # 2020-01-07 12:30:00

Least

接受至少两个字段名称或表达式的列表,并返回最小值,每个参数必须具有相似的类型,用法同Greatest相同

NullIf

接受两个表达式,如果它们相等,则返回None,否则返回第一个

1
2
3
Author.objects.create(name="tony", age=25)
author = Author.objects.annotate(is_equal=NullIf("name", "age")).get()
print(author.is_equal) # tony

Extract

将数据库时间转换成需要的格式

1
2
3
4
5
6
7
class Experiment(models.Model):
start_datetime = models.DateTimeField()
start_date = models.DateField(null=True, blank=True)
start_time = models.TimeField(null=True, blank=True)
end_datetime = models.DateTimeField(null=True, blank=True)
end_date = models.DateField(null=True, blank=True)
end_time = models.TimeField(null=True, blank=True)
1
2
3
4
5
6
7
8
from datetime import datetime
from django.db.models.functions import Extract

start = datetime(2015, 6, 15)
end = datetime(2015, 7, 2)
Experiment.objects.create(start_datetime=start, start_date=start.date(), end_datetime=end, end_date=end.date())
experiment = Experiment.objects.annotate(start_year=Extract('start_datetime', 'year')).get()
print(experiment.start_year) # 2015

也可以这样使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
from datetime import datetime
from django.utils import timezone
from django.db.models.functions import (
ExtractDay,
ExtractMonth,
ExtractQuarter,
ExtractWeek,
ExtractIsoWeekDay,
ExtractWeekDay,
ExtractIsoYear,
ExtractYear,
)
start_2015 = datetime(2015, 6, 15, 23, 30, 1, tzinfo=timezone.utc)
end_2015 = datetime(2015, 6, 16, 13, 11, 27, tzinfo=timezone.utc)
Experiment.objects.create(start_datetime=start_2015, start_date=start_2015.date(),end_datetime=end_2015, end_date=end_2015.date())
Experiment.objects.annotate(
year=ExtractYear('start_date'),
isoyear=ExtractIsoYear('start_date'),
quarter=ExtractQuarter('start_date'),
month=ExtractMonth('start_date'),
week=ExtractWeek('start_date'),
day=ExtractDay('start_date'),
weekday=ExtractWeekDay('start_date'),
isoweekday=ExtractIsoWeekDay('start_date'),
).values('year', 'isoyear', 'quarter', 'month', 'week', 'day', 'weekday','isoweekday').get(end_date__year=ExtractYear('start_date'))
# {'year': 2015, 'isoyear': 2015, 'quarter': 2, 'month': 6, 'week': 25,'day': 15, 'weekday': 2, 'isoweekday': 1}