CODEWITHSUNDEEP
pythondjangodjango-annotate
Paul West
Paul West

Reputation: 111

How to create a weekly summary of dated records in model django

I have a model that has entries for each day. I want to create a queryset with a subtotals (annotates) for a selection of fields in the model.

This is what i have so far.

Here is the model (or some of it!)

class Shiftlog(models.Model):
    date = models.DateField(default=timezone.now)
    onlinetime = models.DurationField()
    trips = models.IntegerField(default=0)
    km = models.IntegerField(default=0)

    def weekcom(self):
        weekcom = self.date - timedelta(days=self.date.weekday())
        return weekcom

Here is the view code that represents what I am trying to achieve.

report = Shiftlog.objects.values('weekcom').annotate(Sum('km'))

Now this does not work. I see from other posts that the annotate functionality only works with model fields.

I appreciate I can create a dictionary of weekcom (weekcommencing rows) and iterate through the model to populate some k,v buckets for each row (weekcom). However, this does not feel right.

I also appreciate that this can be done in SQL directly (I have not gone to this option yet so i don't have the code), but I am trying to avoid this option too.

Any guidance, much appreciated. Thanks!

***** Update.

Thank you both for your responses. Both were very helpful in different ways. With your help I have solved my problem.

Here is my solution.

data = Shiftlog.objects.values('date__iso_year','date__week').annotate(Sum('km'))

for entry in data:
   entry.update({'week_startdate':
        _startweekdate(entry['date__iso_year'],entry['date__week'])})

def _startweekdate(year,weekno):
    weekstartdate = datetime.strptime(str(year)+'-W'+str(weekno)+'-1', "%G-W%V-%w").date()
    return weekstartdate

This yields a nice dictionary I can iterate through for my report given the week start date and the totals.

Upvotes: 3

Views: 1063

Answers (2)

Andrey Nelubin
Andrey Nelubin

Reputation: 3294

from django.db.models.functions import ExtractYear, ExtractWeek


Shiftlog.objects.annotate(
    week=ExtractWeek('date'),   
    year=ExtractYear('date')
).annotate(
     date=F('week')+F('year')
).values(
    'date'
).annotate(
     s=Sum('km')
).values(
    'date',
    's'
).order_by(
   'date'
)

Upvotes: 2

weAreStarsDust
weAreStarsDust

Reputation: 2752

Try this

Shiftlog.objects.values('date__week').annotate(Sum('km'))

Upvotes: 1

Related Questions