下面记录的类为用户提供了一种使用底层数据库提供的函数作为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}