[Django]-Django annotate group by date return object

by

4👍

✅

You can do it like this using TruncDate:

from django.db.models.functions import TruncDate

Flight.objects.annotate(date=TruncDate('origin_scheduled_dep')).values('date').annotate(count=Count('date')).order_by('date')

FYI: If you are using MySQL and using timezone support from django, then use mysql_tzinfo_to_sql to load time zone tables into the MySQL database(reference). This SO answer might help regarding this.

Update

I don’t think your expected result is achievable using only django queryset functions. So, I think you need to use some python functions to achieve it, like this:

from collections import defaultdict  
results = defaultdict(list)

queryset = Flight.objects.annotate(date=TruncDate('origin_scheduled_dep')).order_by('date')

for flight in queryset:
    results[flight.date].append(flight)

for key, value in results.items():
     print(key,value)

Or you can use regroup template tag to display a hierarchical list.

👤ruddra

3👍

You’ll want to use a mix of TruncDate, order_by and itertools.groupby

from itertools import groupby
from operator import attrgetter

queryset = Flight.objects.annotate(
   date=TruncDate('origin_scheduled_dep'),
).order_by('date')
grouped = [
    {'date': date, 'instances': instances}
    for date, instances in itertools.groupby(
       queryset.iterator(), attrgetter('date')
    )
]
👤Simon Charette

0👍

I think it will work as you need but I don’t think it is the best practice for the problem 

from django.db.models import Count

times = Flight.objects.values_list('origin_scheduled_dep', flat=True).annotate(Count('origin_scheduled_dep'))
my_list = []

for time in times:
    my_list.append({'date': time, 'instances': Flight.objects.filter(origin_scheduled_dep=time)})

I tried to do this with Django ORM but I can’t
so i
tested this for a dataset of 330000 with 820 repeated values and some values have more then 2200 instances and the time was good

👤Mohamed Abd El-hameed

Leave a comment