python - Django:按不同列的最新过滤

Question

给定这个 FruitBasket 模型，

class FruitBasket(Model):    fruit = CharField(max_length=128)    count = PositiveIntegerField()

这个示例数据，

id      fruit        count-----   ----------   -----0       apple        101       banana       202       apple        53       banana       30

我想要一个返回以下项目的 Django 查询:

[(2，苹果，5)，(3，香蕉，30)]

本质上，获取“最新”行 per 水果(在这个例子中我将时间戳简化为 rowid。)

Answer 1

https://docs.djangoproject.com/en/dev/ref/models/querysets/#distinct

q = FruitBasket.objects.distinct('fruit')

仅当您使用 postgres 时才有效。

On PostgreSQL only, you can pass positional arguments (*fields) in order to specify the names of fields to which the DISTINCT should apply. This translates to a SELECT DISTINCT ON SQL query. Here’s the difference. For a normal distinct() call, the database compares each field in each row when determining which rows are distinct. For a distinct() call with specified field names, the database will only compare the specified field names.

此外，您必须指定一个 order_by 并且不能按时间戳:

q = FruitBasket.objects.distinct('fruit').order_by('fruit')

When you specify field names, you must provide an order_by() in the QuerySet, and the fields in order_by() must start with the fields in distinct(), in the same order.

For example, SELECT DISTINCT ON (a) gives you the first row for each value in column a. If you don’t specify an order, you’ll get some arbitrary row.

但是，values 可能会让您更接近，如果您可以满足以下要求:distinct/order_by 在相同的顺序中具有相同的值。

q = (
    FruitBasket.objects
    .values('id', 'fruit', 'count')
    .distinct('fruit').order_by('-id')
)

实际上，有时候打破ORM会更好

SELECT id, fruit, count 
FROM FruitBasket
GROUP BY fruit  
ORDER BY id DESC

所以这个查询并不神奇...

SELECT * FROM (SELECT id, fruit, count 
FROM FruitBasket
ORDER BY id DESC) t
GROUP BY t.fruit

这个更好，但有点丑。

自行优化:

q = FruitBasket.objects.raw("""\
    SELECT * FROM 
    (
        SELECT id, fruit, count 
        FROM FruitBasket 
        ORDER BY id DESC
    ) t
    GROUP BY t.fruit
""")