Django：不同字段的最早条目 [英] Django: oldest entry for distinct fields

问题描述

我有以下模型（出于示例目的被简化）：

I have the following models (simplified for sample purposes):

class Execution(models.Model):
    name = models.CharField(null=False, max_length=30)
    creation_date = models.DateTimeField(auto_now_add=True, null=False)

class Post(models.Model):
    class Meta:
        unique_together = ('code', 'secondary_code', 'execution')

    code = models.CharField(null=False, max_length=30)
    secondary_code = models.CharField(null=False, max_length=3)
    execution = models.ForeignKey(Execution, null=False, on_delete=models.CASCADE)
    text = models.TextField()

在数据库中，我有以下实例：

On the database I have the following instances:

execution_1 = Execution('Execution 1', '2019-01-01')
execution_2 = Execution('Execution 2', '2019-01-02')

post_1 = Post('123', '456', execution_1, 'lorem')
post_2 = Post('789', '999', execution_1, 'ipsum')
post_3 = Post('789', '999', execution_2, 'dolor')

我想检索所有帖子，对于 code 和是唯一的（因此，在 post_2 和 post_3 ，因为它们具有相同的代码和 secondary_code ），然后根据最早的 execut $ 选择一个（因此，在这种情况下，我想要 post_1 和 post_2 ，因为 post_2 的 execution 较旧 execution of post_3 ）。

I want to retrieve all the posts, unique for code and secondary_code (so, only one between post_2 and post_3 since they have the same code and secondary_code) and chose the one to get based on the oldest execution (so, in this case, I want post_1 and post_2 since the execution of post_2 is older that the execution of post_3).

我需要同时支持Postgres和sqlite3 3.18.0，因此，由于sqlite，我无法使用窗口功能。

I need to support both Postgres and sqlite3 3.18.0, so, because of sqlite, I cannot use window functions.

如何执行此操作？

推荐答案

newer_post = Post.objects.filter(code=OuterRef('code'), 
    secondary_code=OuterRef('secondary_code'), 
    execution__creation_date__gt=OuterRef('execution_dt'), 
)

posts = Post.objects.all().annotate(execution_dt=execution__creation_date, )

latest_posts = posts.\
    annotate(has_newer_post=Exists(newer_post), ).\
    filter(has_newer_post=False, )

这篇关于Django：不同字段的最早条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！