Return object when aggregating grouped fields in Django

Question

Assuming the following example model:

# models.py
class event(models.Model):
    location = models.CharField(max_length=10)
    type = models.CharField(max_length=10)
    date = models.DateTimeField()
    attendance = models.IntegerField()

I want to get the attendance number for the latest date of each event location and type combination, using Django ORM. According to the Django Aggregation documentation, we can achieve something close to this, using values preceding the annotation.

... the original results are grouped according to the unique combinations of the fields specified in the values() clause. An annotation is then provided for each unique group; the annotation is computed over all members of the group.

So using the example model, we can write:

event.objects.values('location', 'type').annotate(latest_date=Max('date'))

which does indeed group events by location and type, but does not return the attendance field, which is the desired behavior.

Another approach I tried was to use distinct i.e.:

event.objects.distinct('location', 'type').annotate(latest_date=Max('date'))

but I get an error

NotImplementedError: annotate() + distinct(fields) is not implemented.

I found some answers which rely on database specific features of Django, but I would like to find a solution which is agnostic to the underlying relational database.

Well you can never be 100% agnostic: all features django offers in the ORM are to some extend related to common features a *relational* database offers, but there are other types of databases like *graphical* and cascading (MongoDB for example). — Willem Van Onsem, Jun 10 '18 at 20:22
You are right, I should clarify that I meant relational. I will update my question. — Demetris, Jun 10 '18 at 20:24
Sorry, that should be `attendance`. I will update once again! — Demetris, Jun 10 '18 at 20:25
So if I understand what you are trying to do correctly, you are trying to figure out how many people will be in attendance for an event, like a concert? — Carl Brubaker, Jun 11 '18 at 00:44
Well the model is just an example, but the objective is to get the attendance of the chronologically last event for each event location and type combination. — Demetris, Jun 11 '18 at 05:44

Carl Brubaker · Answer 1 · 2018-06-12T10:34:59.710

0

Alright, I think this one might actually work for you. It is based upon an assumption, which I think is correct.

When you create your model object, they should all be unique. It seems highly unlikely that that you would have two events on the same date, in the same location of the same type. So with that assumption, let's begin: (as a formatting note, class Names tend to start with capital letters to differentiate between classes and variables or instances.)

# First you get your desired events with your criteria.
results = Event.objects.values('location', 'type').annotate(latest_date=Max('date'))

# Make an empty 'list' to store the values you want.
results_list = []

# Then iterate through your 'results' looking up objects
# you want and populating the list.
for r in results:
    result = Event.objects.get(location=r['location'], type=r['type'], date=r['latest_date'])
    results_list.append(result)

# Now you have a list of objects that you can do whatever you want with.

You might have to look up the exact output of the Max(Date), but this should get you on the right path.

edited Jun 12 '18 at 10:34

answered Jun 11 '18 at 11:15

Carl Brubaker

1,602
11
26

I don't think this works. The annotate returns a dict which does not contain the `attendance` key. – Demetris Jun 11 '18 at 19:38
Did you try adding attendance to the values? – Carl Brubaker Jun 11 '18 at 19:52
You cant do that because that would create unique location/type/attendance combinations before annotating. I just need the unique location/type combinations. – Demetris Jun 11 '18 at 20:22
Thank you for the updated answer. Unfortunately I have already tried that and it doesn't work. The second `values` following the `annotate` "breaks" the grouping of unique combinations provided by the first `values`. As a result, you get the attendance for every event object. – Demetris Jun 12 '18 at 07:20
Ok. This is my last shot. Updated answer. – Carl Brubaker Jun 12 '18 at 10:35
As stated in my original question, I am looking for a solution using Django ORM directly rather than looping through. Thank you for your effort though. – Demetris Jun 12 '18 at 13:08
Sorry. Guess I didn’t understand your question. – Carl Brubaker Jun 12 '18 at 13:26

Return object when aggregating grouped fields in Django

1 Answers1