Django Multiple Queries-CodePudding

I'm using lots of queries to find the sum of fields [ft_day, ft_night] in multiple records for specified date intervals e.g. last 7 days, last 6 months etc.

This is needed to display a summary of total times at the aforementioned date intervals in a template.

ft_total is a property in Models

Is there a more efficient way to do this? Not really sticking to DRY here.

Thanks for your help.

views.py get_context_data()

## shortened ##
context['last_6_months_rpic'] = RPLogbookEntry.objects.filter(user=self.request.user, user_role='RPIC', entry_date__range=(datetime.datetime(int(self.date_now.year - (self.date_now.month - 5)/12), (self.date_now.month - 6) % 12 - 1, self.date_now.day), self.date_now)
).annotate(ft_total = F('ft_day')   F('ft_night')).aggregate(Sum('ft_total'))
context['last_12_months_rpic'] = RPLogbookEntry.objects.filter(user=self.request.user, user_role='RPIC', entry_date__range=(datetime.datetime(int(self.date_now.year - (self.date_now.month - 12)/12), (self.date_now.month - 12) % 12 - 1, self.date_now.day), self.date_now)
).annotate(ft_total = F('ft_day')   F('ft_night')).aggregate(Sum('ft_total'))

CodePudding user response：

You can pass a filter to Sum, this allows you to do multiple sums with different filters in the same aggregate

RPLogbookEntry.objects.filter(
    user=self.request.user,
    user_role='RPIC',
).annotate(
    ft_total=F('ft_day')   F('ft_night')
).aggregate(
    last_6_months_rpic=Sum('ft_total', filter=Q(entry_date__range=(datetime.datetime(int(self.date_now.year - (self.date_now.month - 5)/12), (self.date_now.month - 6) % 12 - 1, self.date_now.day), self.date_now))),
    last_12_months_rpic=Sum('ft_total', filter=Q(entry_date__range=(datetime.datetime(int(self.date_now.year - (self.date_now.month - 12)/12), (self.date_now.month - 12) % 12 - 1, self.date_now.day), self.date_now))),
)