python之如何在 Django 中获得按相关模型分组的最高值模型

我想在 Django 中执行这个查询:

对于每个传感器，选择它的最新消息

在 SQL 中它会是这样的

SELECT * FROM (SELECT * FROM messages order by date_entered DESC) as order GROUP BY sensor_id 

模型是这样定义的

class BaseModel(models.Model): 
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False) 
    date_entered = models.DateTimeField(auto_now_add=True) 
    date_modified = models.DateTimeField(auto_now=True) 
    deleted = models.IntegerField(default=0) 
 
    class Meta: 
        abstract = True 
        ordering = ['date_modified'] 
 
class Device(BaseModel): 
 
    user =              models.ForeignKey(User, on_delete=models.SET_NULL, blank=True, null=True) 
 
    name =              models.CharField(max_length=255, blank=False) 
    identifier =        models.CharField(max_length=31, blank=False) 
    imei =              models.CharField(max_length=31, blank=False) 
    status =            models.CharField(max_length=31, blank=False) 
    settings =          models.TextField() 
 
 
class DeviceMessage(BaseModel): 
 
 
    device =            models.ForeignKey(Device, on_delete=models.SET_NULL, blank=True, null=True) 
    user =              models.ForeignKey(User, on_delete=models.SET_NULL, blank=True, null=True) 
 
    latitude =          models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True) 
    longitude =         models.DecimalField(max_digits=9, decimal_places=6, blank=True, null=True) 
    altitude =          models.DecimalField(max_digits=7, decimal_places=2, blank=True, null=True) 
    signal_strength =   models.DecimalField(max_digits=5, decimal_places=2, blank=True, null=True) 
    battery =           models.DecimalField(max_digits=5, decimal_places=2, blank=True, null=True) 
 
    satellites =        models.IntegerField(blank=True, null=True) 
    size =              models.IntegerField(blank=True, null=True) 
 
    raw =               models.TextField(blank=True, null=True) 

是否可以在 django 中实现这一点？

基本上就是这个问题Using ORDER BY and GROUP BY together

请您参考如下方法:

从 Django 1.11 开始有 Subquery feature您可以用来注释数据:

from django.db.models import OuterRef, Subquery 
 
latest = DeviceMessage.objects.filter(device_id=OuterRef('pk')).order_by('-date_entered') 
 
devices = Device.objects.annotate(latest_message_id=Subquery(latest.values('pk')[:1])) 
message_ids = [d.latest_message_id for d in devices] 
for message in DeviceMessage.objects.filter(pk__in=message_ids).select_related('device'): 
    print(device.name, message) 

……