Django查询:按字段分组并获取每个组的最新条目

时间:2022-06-29 11:45:53

I have the following table in Django-1.11:

我在Django-1.11中有如下表格:

class Market(models.Model):
    slug = models.SlugField(...)
    active = models.DateTimeField(...)

It would be great if the slug was a foreign key to avoid duplicate values but this is not the case, I am coping with a third party app.

如果slug是一个外键,可以避免重复使用,那就太棒了。但我现在正在开发一个第三方应用。

data = [
    {'id': 1, 'slug': 'test-1', 'active': 'datetime.datetime(2017, 9, 18, 10, 43, 8, 581046, tzinfo=<UTC>)'}, 
    {'id': 2, 'slug': 'test-1', 'active': 'datetime.datetime(2017, 9, 20, 10, 43, 8, 581046, tzinfo=<UTC>)'}, 
    {'id': 3, 'slug': 'test-2', 'active': 'datetime.datetime(2017, 9, 10, 10, 43, 8, 581046, tzinfo=<UTC>)'},
    {'id': 4, 'slug': 'test-2', 'active': 'datetime.datetime(2017, 9, 19, 10, 43, 8, 581046, tzinfo=<UTC>)'},
]

I am looking for a query which will return the latest entry with slug test-1 and the latest entry with slug test-2.

我正在寻找一个查询,它将返回带有slug test-1的最新条目,以及带有slug test-2的最新条目。

Using annotate I just receive the results with their datetime:

使用注释,我只是用它们的datetime收到结果:

Market.objects.values('slug').annotate(Max('active'))
Out[11]: <QuerySet [
{'active__max': datetime.datetime(2017, 9, 18, 10, 43, 8, 581046, tzinfo=<UTC>), 'slug': 'test-1'},
{'active__max': datetime.datetime(2017, 9, 20, 10, 43, 8, 581046, tzinfo=<UTC>), 'slug': 'test-1'},
{'active__max': datetime.datetime(2017, 9, 10, 10, 43, 8, 581046, tzinfo=<UTC>), 'slug': 'test-2'},
{'active__max': datetime.datetime(2017, 9, 19, 10, 43, 8, 581046, tzinfo=<UTC>), 'slug': 'test-2'}
]>

This result does not seem to follow the docs. What am I doing wrong? Is it the SlugField that does not permit "grouping"?

这个结果似乎并不遵循文档。我做错了什么?是鼻涕虫不允许“分组”吗?

1 个解决方案

#1


1  

looks like you need to add empty order_by() by the default-ordering-or-order-by in the result query can be added extra fields to the group by.

看起来您需要添加一个空的order_by(),由结果查询中的default-order -or- by -by -by可以向组by添加额外的字段。

try it:

试一试:

Market.objects.values('slug').annotate(Max('active')).order_by()

#1


1  

looks like you need to add empty order_by() by the default-ordering-or-order-by in the result query can be added extra fields to the group by.

看起来您需要添加一个空的order_by(),由结果查询中的default-order -or- by -by -by可以向组by添加额外的字段。

try it:

试一试:

Market.objects.values('slug').annotate(Max('active')).order_by()