CODEWITHSUNDEEP
pythondjangodjango-models
Cyzanfar
Cyzanfar

Reputation: 7136

Django annotate group by date return object

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

Upvotes: 3

Views: 1642

Answers (3)

ruddra
ruddra

Reputation: 51938

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.

Upvotes: 4

Mohamed Abd El-hameed
Mohamed Abd El-hameed

Reputation: 420

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

Upvotes: 0

Simon Charette
Simon Charette

Reputation: 5116

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

Upvotes: 3

Related Questions