I have following model in django
Business ID
Business Name
Business Revenue
Date
Here is the sample data:
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.
Here is the output I want:
Business ID | Business Name | Business Revenue | Date
1 B1 2000 2013-04-13
2 B2 24000 2014-09-02
I have tried this:
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
Try this:
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)
This link can help u.
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments