按每个组的最近日期获取第一个元素 [英] Get First element by the recent date of each group

问题描述

我在Django中有以下模型

I have following model in django

Business ID
Business Name
Business Revenue
Date

以下是示例数据:

Business ID | Business Name | Business Revenue | Date
1             B1              1000               2012-10-13
2             B2              20000              2013-10-16
1             B1              2000               2013-04-13
2             B2              24000              2014-09-02

我想获取所有具有最新日期的企业.

I want to fetch all businesses having latest date.

这是我想要的输出:

Business ID | Business Name | Business Revenue | Date
1             B1              2000               2013-04-13
2             B2              24000              2014-09-02

我已经尝试过了:

model.objects.filter(date__gte = date.today()).order_by('business_id', '-date')

这给了我一组业务，每个业务组中最顶部的都是最新业务.如何省略其余的行?我需要帮助

This is giving me group of businesses having latest business in each group at the top of it. How do I omit the rest of rows? I need some assistance

推荐答案

尝试一下:

from django.db.models import Q

group_dict = Model.objects.values('business_id').annotate(max_date=Max('date')).order_by()

params = Q()
for obj in group_dict:
    params |= (Q(business_id__exact=obj['business_id']) & Q(date=obj['max_date']))

qs = Model.objects.filter(params)

此链接可以帮助您.

如果values()子句位于annotate()子句之前，则所有注释 将自动添加到结果集中.但是，如果 values()子句在annotate()子句之后应用，您需要 明确包括聚合列.

If the values() clause precedes the annotate() clause, any annotations will be automatically added to the result set. However, if the values() clause is applied after the annotate() clause, you need to explicitly include the aggregate column.

这篇关于按每个组的最近日期获取第一个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！