CODEWITHSUNDEEP
pythondjangopostgresqldjango-queryset
roob
roob

Reputation: 2529

Django orm - how to get distinct items of field within query of distinct dates

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?

Upvotes: 2

Views: 1008

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476584

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.

Upvotes: 2

Related Questions