query to loop over records by date in django-CodePudding

I am trying to find a better way to loop over orders for the next seven days including today, what I have already:

unfilled_orders_0 = Model.objects.filter(delivery_on__date=timezone.now()   timezone.timedelta(0))
context['todays_orders'] = unfilld_orders_0.aggregate(field_1_sum=Sum('field_1'), field_2_sum=Sum('field_2'),field_3_sum=Sum('field_3'), field_4_sum=Sum('field_4'),field_5_sum=Sum('field_5'))

I'm wondering if I can somehow avoid having to do this seven times--one for each day. I assume there is a more efficient way to do this.

CodePudding user response：

You can do this with a single ORM / db query, by providing Sum with an extra filter argument:

days_ahead = 7
fields = ["field_1", "field_2", ...]
aggregate_kwargs = {
    f"s_{field}_{day}": Sum(field, filter=Q(delivery_on__date=now timedelta(days=day)))
    for field in fields
    for day in range(days_ahead)
}
unfilled_orders = Model.objects.filter(delivery_on__date__lt=now timedelta(days=days_ahead)
context.update(unfilled_orders.aggregate(**aggregate_kwargs))

CodePudding user response：

You can approach it with a for loop and store the data in the context like so

from django.utils import timezone
from django.db.models import Sum
context = {}
for i in range(7):
    qs = Model.objects.filter(delivery_on__date=(timezone.now()   timezone.timedelta(i)).date())
    context = {}
    context[f'orders_{i}'] = qs.aggregate(
        field_1_sum=Sum('field_1'),
        field_2_sum=Sum('field_2'),
        field_3_sum=Sum('field_3'),
        field_4_sum=Sum('field_4'),
        field_5_sum=Sum('field_5'))

This query will hit 7 times the database, otherwise you can use another approach which will hit the db only once

context = {}
qs = Model.objects.filter(delivery_on__date__lte=timezone.now()-timezone.timedelta(days=7)).order_by('delivery_on')
dates = qs.values('delivery_on__date', flat=True).distinct()
for i in dates:
    _qs = qs.filter(create_ts__date=i)
    context[f'orders_{i}'] = _qs.aggregate(
        field_1_sum=Sum('field_1'),
        field_2_sum=Sum('field_2'),
        field_3_sum=Sum('field_3'),
        field_4_sum=Sum('field_4'),
        field_5_sum=Sum('field_5'))

You define how many days backwards the qs will be including all orders then distinct the dates and filter the already filtered qs for the dates.