代码之家 › 专栏 › 技术社区 › lmiguelvargasf

如何按DateTimeField排序并基于ForeignKey选择不同的元素?

postgresql django python

-1

lmiguelvargasf · 技术社区 · 6 年前

class Food(models.Model):
    """Stores a food entry"""
    name = models.CharField(max_length=50)
    image_id = models.CharField(max_length=20)
    description = models.TextField()

class SurveyResult(models.Model):
    """Stores survey food result based on emoji and personality"""
    emoji = models.CharField(max_length=10)
    personality = models.CharField(max_length=50)
    food = models.ForeignKey(Food, on_delete=models.CASCADE)
    timestamp = models.DateTimeField(auto_now_add=True)
    rating = models.PositiveSmallIntegerField(default=0)

'-timestamp' ,只需为 'food' . 这就是我所尝试的:

SurveyResult.objects.order_by('-timestamp').distinct('food')

编程错误:SELECT DISTINCT ON表达式必须与初始ORDER BY表达式匹配第1行:选择DISTINCT ON(“recommender\u surveyresult.”“food\u id”)“r。。。

我读了几个问题的答案,我发现一个答案基本上是这样的:

SurveyResult.objects.order_by('food', '-timestamp').distinct('food')

然而,我没有得到我想要的那是最新的 SurveyResult food .

我怎样才能做到这一点?

假设我的数据库中有下表:

# surveyresults
id emoji personality food timestamp            rating
1  :)    Famous US     1  2018-01-01 - 9:00      1
2  XD    Famous CN     3  2018-01-01 - 9:10      2
3  :)    Famous NZ     5  2018-01-01 - 9:15      3
4  :(    Famous FR     5  2018-01-01 - 9:35      4
5  XD    Famous US     1  2018-01-01 - 9:45      5
6  ;)    Famous JP     4  2018-01-01 - 9:55      5
7  :o    Famous SP     4  2018-01-01 - 10:00     5

id emoji personality food timestamp            rating
7  :o    Famous SP     4  2018-01-01 - 10:00     5
5  XD    Famous US     1  2018-01-01 - 9:45      5
4  :(    Famous FR     5  2018-01-01 - 9:35      4
2  XD    Famous CN     3  2018-01-01 - 9:10      2

我也想知道,是否有可能实现这一点只是一个查询。

2 回复 | 直到 6 年前

lmiguelvargasf 6 年前

您可以使用 subquery

SurveyResult.objects.filter(
    id__in=SurveyResult.objects.order_by('food', '-timestamp').distinct('food')
).order_by('-timestamp')

说明:

所以基本上涉及多个评估 query ,在您的情况下,我们正在评估两个查询。上面的SQL queryset

SELECT "id", "emoji", "personality", "food", "timestamp", "rating"
FROM "appname_surveyresult"
WHERE id IN (
    SELECT "id", DISTINCT ON ("food") 
    FROM "appname_surveyresult"
    ORDER BY "food" ASC, "timestamp" DESC 
)
ORDER BY "timestamp" DESC

因此,首先内部(就像数学)查询求值,其结果成为外部查询的输入,从而导致 . 阅读 this 更多。

Kinyanjui Karanja 6 年前

class SurveyResult(models.Model):
    ...

   class Meta:
        ordering = ('-timestamp',)