时间戳

首页 > 解决方案 > Django annotate related field

python - Django annotate related field

问题描述

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

sensors = Sensor.objects.all()

Here is the model:

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)

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')

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

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])
)

or using prefetch:

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

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

标签： pythondjangoorm

解决方案

我会反过来做：您当前尝试检索 all Sensors，然后获取他们最新的关联Record. 为什么不检索所有Records具有Sensor这样的东西sensor.last_record_at == record.created？

这边走：

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

会做的工作。

这假设对于给定的传感器，您永远不会同时有两条记录，否则，您可能会获得多次相同的记录，Sensor并且每个记录Sensor都有日期。Recordlast_record_at

推荐阅读