Django ORM - 获取组的最新记录 [英] Django ORM - Get latest record for group

问题描述

假设我们有一个 Django ORM 模型 Meetup，其定义如下:

class Meetup(models.Model):语言 = 模型.CharField()日期 = 模型.DateField(auto_now=True)

我想获取每种语言的最新聚会.

看来您可以使用 Django Aggregates 来实现这一点查找简单:

Meetup.objects.annotate(latest_date=Max("date")).values("language", "latest_date")

在我看来，这应该获取每种语言的最新"聚会.但事实并非如此:

<预><代码>>>>Meetup.objects.create(language='python')<聚会:聚会对象>>>>Meetup.objects.create(language='python')<聚会:聚会对象>>>>Meetup.objects.create(language='node')<聚会:聚会对象>>>>Meetup.objects.create(language='node')<聚会:聚会对象>>>>Meetup.objects.annotate(latest_date=Max("date")).values("language", "latest_date").count()4

我希望能得到两个最新的 Python 和 Node 聚会！

如何构建一个查询，只获取每种语言的最新聚会?

附注.我使用 MySQL 作为我的后端.

解决方案

把你的 values 子句放在 annotate 之前.

来自 聚合文档:

<块引用>

如果 values() 子句在 annotate() 之前，将使用 values() 子句描述的分组来计算注释.

但是，如果 annotate() 子句在 values() 子句之前，注释将在整个查询集上生成.在这种情况下，values() 子句仅约束在输出时生成的字段.

所以应该这样做:

Meetup.objects.values('language').annotate(latest_date=Max('date'))

Imagine we have the Django ORM model Meetup with the following definition:

class Meetup(models.Model):
    language = models.CharField()
    date = models.DateField(auto_now=True)

I'd like to fetch the latest meetup for each language.

It would seem you could use Django Aggregates to make this lookup easy:

Meetup.objects.annotate(latest_date=Max("date")).values("language", "latest_date")

In my mind this should fetch the "latest" meetup for each language. But that's not the case:

>>> Meetup.objects.create(language='python')
<Meetup: Meetup object>
>>> Meetup.objects.create(language='python')
<Meetup: Meetup object>
>>> Meetup.objects.create(language='node')
<Meetup: Meetup object>
>>> Meetup.objects.create(language='node')
<Meetup: Meetup object>
>>> Meetup.objects.annotate(latest_date=Max("date")).values("language", "latest_date").count()
4

I expected to get just the two latest Python and Node meetups!

How can I construct a query that will fetch only the latest meetups for each language?

PS. I'm using MySQL as my backend.

解决方案

Put your values clause before the annotate.

From the aggregation docs:

If the values() clause precedes the annotate(), the annotation will be computed using the grouping described by the values() clause.

However, if the annotate() clause precedes the values() clause, the annotations will be generated over the entire query set. In this case, the values() clause only constrains the fields that are generated on output.

So this should do it:

Meetup.objects.values('language').annotate(latest_date=Max('date'))

这篇关于Django ORM - 获取组的最新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！