Django:按ID [多对一关系]获取最后一条记录 [英] Django: Get last record by ID [many-to-one relationship]

问题描述

我正在尝试使用Django模型中的多对一关系将一个表中的最后一条记录连接到另一个表.这是我的Django模型:

I'm trying to get the last record in one table connected to another one using many-to-one relationship in django models. Here's my django models:

class DataCollecttion(models.Model):
    default_name = models.CharField(max_length=100)

class NameHistory(models.Model):
    old_name = models.CharField(max_length=100)
    collection_data = models.ForeignKey(DataCollection, on_delete=models.CASCADE, null=True)

在这里，我为 DataCollection 表创建了一个示例数据:

Here I created a sample data for DataCollection table:

这是 NameHistory 表的示例数据:

我在这里想要过滤或获取每个 collection_data_id 中的 NameHistory 中的最后一条记录(红色矩形内的记录)，并将其显示在我的视图中.简而言之，我想获得这些行以及如何在ORM查询中做到这一点:

What I want here is to filter or get the last record in NameHistory in each collection_data_id (the records inside the red rectangle) and display it in my views. So in short I want to get these lines and how can I do it in ORM Query:

sample3
test2
data1

推荐答案

是否需要

窗口函数提供了一种在分区上应用函数的方法.窗口函数不同于普通的聚合函数，它为group by定义的每个集合计算最终结果，而窗口函数则对帧和分区进行运算，并为每一行计算结果.

Window functions provide a way to apply functions on partitions. Unlike a normal aggregation function which computes a final result for each set defined by the group by, window functions operate on frames and partitions, and compute the result for each row.

对于您的架构设计:

from django.db.models import F, Window
from django.db.models.functions.window import FirstValue

( DataCollecttion
  .objects
  .annotate(
     first_old_name=Window(
        expression=FirstValue('namehistory__old_name'),
        partition_by=[F('id'), ],
        order_by=F('namehistory__id').desc()
     )
   )
  .values_list('first_old_name', flat=True)
  .distinct()
 )

这应该返回预期的列表.

This should return the expected list.

这篇关于Django:按ID [多对一关系]获取最后一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！