Django注释相关字段 [英] Django annotate related field

问题描述

我想优化一个简单的Django查询以预取所有最新值.

I want to optimize a simple django query to prefetch all the latest values.

sensors = Sensor.objects.all()

这是模型:

class Sensor:
    last_record_at = models.DateTimeField(null=True, blank=True)

class Record:
    value = models.FloatField()
    sensor = models.ForeignKey('hardware.Sensor', on_delete=models.CASCADE)
    created = models.DateTimeField(auto_now_add=True)

之前，Sensor模型具有要记录的外键(最后一条记录)，只需添加以下内容即可检索所有记录:

Before, Sensor model had a foreign key (last record) to record and all the records could be retrieved simply by adding:

.select_related('last_record')

为了优化数据库，我删除了该外键并将其替换为名为last_record_at的日期时间字段.

To optimize the database, I removed that foreign key and replaced it with a datetime field named last_record_at.

我正在使用Django 2.0，我想知道是否存在一种漂亮的ORM方式，可以使用子查询，批注或预取来检索一个(或两个)查询中的所有传感器和最后记录.

I am using Django 2.0 and I am wondering if there is a pretty ORM way to retrieve all the sensors and last records in one (or two) query using subqueries, annotation or prefetching.

类似的东西(不起作用):

Something like (it does not work):

record_sub_query = Record.objects.filter(
    created=OuterRef('last_record_at'), 
    sensor_id=OuterRef('pk')
) 
sensors = Sensor.objects.all().annotate(
    last_record=Subquery(record_sub_query[:1])
)   

或使用预取:

sensors = Sensor.objects.all().prefetch_related(
    Prefetch(
        'records', 
        queryset=Record.objects.filter(created=F('sensor__last_record_at'))
    )
)

我看到的唯一选择是针对这个相当简单的问题编写Raw SQL.

The only alternative I see is writing Raw SQL for this rather simple problem.

推荐答案

我将采用另一种方法:您当前尝试检索所有 Sensors ，然后获取它们的最新关联的 Record.为什么不检索具有 Sensor 的所有 Records ，例如 sensor.last_record_at == record.created ?

I would do it the other way around: you currently try to retrieve all Sensors, then get their latest associated Record. Why not retrieve all Records that have a Sensor such that sensor.last_record_at == record.created ?

这种方式:

Records.objects.filter(sensor__last_record_at=F('created')).select_related('sensor')

会做这份工作.

这假设对于给定的传感器，您永远不会同时有两个记录，否则，您可能会获得相同的 Sensor 数倍的数据，并且每个 Sensor 都有一个具有 last_record_at 日期的 Record .

This assumes you never have two records at the same time for a given sensor, otherwise, you could get several times the same Sensor, and that every Sensor has a Record with the last_record_at date.

这篇关于Django注释相关字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！