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 :)

Answer 1

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.