ProgrammingError：在使用order_by并在Django中一起区分时 [英] ProgrammingError: when using order_by and distinct together in django

问题描述

我有一个类似下面的模型

I have a model like below

class ProductScore(models.Model):
    client = models.ForeignKey(User)
    created = models.DateTimeField(default=datetime.datetime.now)
    score = models.IntegerField()
    scale = models.ForeignKey(Product) 

到目前为止，我正在使用以下查询从上述模型中过滤出重复项

As of now i am using the below query to filter out the duplicates from the above model

scores = ProductScore.objects.filter(client=request.user).distinct('scale')

通过上面的查询，它返回的是唯一的结果，但是很旧（创建日期很旧），例如，如果上面的表 ProductScore 包含10条重复记录，其中昨天创建了5条记录，今天创建了5条记录，上面的查询返回了昨天创建的5条唯一记录。

By the above query it was returning the unique results but are old(created date was very old), i mean for example if the above table ProductScore has 10 duplicate records in which 5 of them are created yesterday and 5 of them are created today, the above query is returning 5 unique records which are created yesterday.

但是我想要主要是最近（即今天）创建且唯一的记录，所以我尝试如下所示

But i want the records which are created mostly recently(i.e., today) and unique so i tried like below

scores = ProductScore.objects.filter(client=request.user).order_by('created').distinct('scale')

无法正常工作并抛出某些编程错误异常

which was not working and throwing some programming error exception

*** ProgrammingError: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT DISTINCT ON ("product_productscore"."scale...
                            ^

那么我怎么得到上表中最近创建的唯一记录？

So how can i get the most recently created unique records form the above table ?

推荐答案

PostgreSQL要求您这样做：

PostgreSQL is asking you to do this:

ProductScore.objects.filter(client=request.user).order_by('scale', '-created').distinct('scale')

...按创建的排序您是每个重复项中最新的一个，尽管您的整体查询结果将按 scale 字段

...ordering by -created will give you the most recent of each duplicate, though your overall query results will be ordered by scale field

这篇关于ProgrammingError：在使用order_by并在Django中一起区分时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！