Django annotate group by date return object

Question

I'm using Django's annotate from the aggregate doc.

My goal is to group a model by date and return all objects associated with each date in this form:

[{
  'date': datetime.datetime(2019, 6, 22, 11, 35), 
  'instances': [<Model: (1)>, <Model: (2)>  ]
},
{
  'date': datetime.datetime(2019, 6, 21, 11, 35),
  'instances': [<Model: (3)>, <Model: (6)>  ]
},]

I tried this query:

Flight.objects.values('origin_scheduled_dep').annotate(Count('origin_scheduled_dep')).order_by('origin_scheduled_dep')

But that's returning the values I specified:

<QuerySet [{'origin_scheduled_dep': datetime.datetime(2019, 6, 22, 11, 35), 'origin_scheduled_dep__count': 1}, {'origin_scheduled_dep': datetime.datetime(2019, 6, 22, 15, 40), 'origin_scheduled_dep__count': 1}, {'origin_scheduled_dep': datetime.datetime(2019, 6, 22, 22, 0), 'origin_scheduled_dep__count': 2}]>

Thanks for any help, always :)

In your sample output you're showing that you want the object instances themselves, but in your sample code you imply that you just want the count. Which is it? — Kevin Christopher Henry, Jul 16 '19 at 00:57
In other words, ``, is the default string value of an instance, so implies that you have or want the instance. If you just want the count, your output might instead look something like `{'date': ..., 'instance_count': 2}`. — Kevin Christopher Henry, Jul 16 '19 at 01:33
I'd like to return the actual model instance grouped by date where the date has it's own attribute value for all flights departing on that date — Cyzanfar, Jul 16 '19 at 16:30

ruddra · Accepted Answer · 2019-07-18T14:28:13.290

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.

edited Jul 18 '19 at 14:28

answered Jul 16 '19 at 04:01

ruddra

50,746
7
78
101

thanks for the answer but this returns only the flight object. I'd like them grouped by date like I showed in my question – Cyzanfar Jul 16 '19 at 16:31
Sorry, I missed `values('date')`. I have updated my answer – ruddra Jul 16 '19 at 16:44
2

Actually this query still returns the count of the object instead of the objects itself. See the output i'd like to get in my question – Cyzanfar Jul 17 '19 at 14:44

score 3 · Answer 2 · answered Jul 16 '19 at 18:08

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')
    )
]

Mohamed Abd El-hameed · Answer 3 · 2019-07-18T13:23:52.830

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

Django annotate group by date return object

3 Answers3

Update