2

I have the following models:

class Event(models.Model):
    date = models.DateTimeField()
    event_type = models.ForeignKey('EventType')

class EventType(models.Model):
    name = models.CharField(unique=True)

I am trying to get a list of all dates, and what event types are available on that date.

Each item in the list would be a dictionary with two fields: date and event_types which would be a list of distinct event types available on that date.

Currently I have come up with a query to get me a list of all distinct dates, but this is only half of what I want to do:

query = Event.objects.all().select_related('event_type')
results = query.distinct('date').order_by('date').values_list('date', flat=True)

Now I can change this slightly to get me a list of all distinct date + event_type combinations:

query = Event.objects.all().select_related('event_type')
results = query.order_by('date').distinct('date', 'event_type').values_list('date', 'event_type__name')

But this will have an entry for each event type within a given date. I need to aggregate a list within each date.

Is there a way I can construct a queryset to do this? If not, how would I do this some other way to get to the same result?

roob
  • 2,419
  • 3
  • 29
  • 45

1 Answers1

2

You can perform such aggregate with the groupby function of itertools. It is a requirement that the elements appearch in "chunks" with respect to the "grouper criteria". But this is the case here, since you use order_by.

We can thus write it like:

from itertools import groupby
from operator import itemgetter

query = (Event.objects.all.select_related('event_type')
                         .order_by('date', 'event_type')
                         .distinct('date', 'event_type')
                         .values_list('date', 'event_type__name'))
result = [
    { 'date': k, 'datetypes': [v[1] for v in vs]}
    for k, vs in groupby(query, itemgetter(0))
]

You also better use 'event_type' in the order by criterion.

This will result in something like:

[{'date': datetime.date(2018, 5, 19), 'datetypes': ['Famous person died',
                                                    'Royal wedding']},
 {'date': datetime.date(2018, 5, 24), 'datetypes': ['Famous person died']},
 {'date': datetime.date(2011, 5, 25), 'datetypes': ['Important law enforced',
                                                    'Referendum']}]

(based on quick Wikipedia scan of the last days in May).

The groupby works in linear time with the number of rows returned.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • Thanks this is great. I was getting `TypeError: tuple indices must be integers, not str` until I changed `v['event_type__name']` to `v[1]` and `itemgetter('date')` to `itemgetter(0).` Do you know if this is an issue with my version of Python? I am using Python2.7 with Django 1.8.X – roob Jun 06 '18 at 21:38
  • @rawbeans: arrgh... I confused `values` with `values_list`. The former returns a dictionary with column names mapped on values. Good catch! – Willem Van Onsem Jun 06 '18 at 21:41
  • Also, the format of result is different than what I asked for. I wanted an array of dictionaries. Each dictionary has two keys `date` and `event_types`. This was any easy change to make however by changing the dictionary comprehension to a list comprehension. – roob Jun 06 '18 at 21:53
  • @rawbeans: ah, ok that was not clear for me. Aynway, I've edited the question such that it is clear for future readers. – Willem Van Onsem Jun 06 '18 at 22:01