How to aggregate records in django based on multiple criteria

Question

Lets say, I have following models in my Django app.

class EventGroup
    name = models.CharField()

class Event(models.Model):
    name = models.CharField()
    group = models.ForeignKey(EventGroup)

class Severity(models.Model):
    name = models.CharField()
    group = models.ForeignKey(EventGroup)

class LogEntry(models.Model):
    timestamp = models.DateTimeField(auto_now_add=True)
    event = models.ForeignKey('Event')
    severity = models.ForeignKey('Severity')

What is the optimal way to find latest log entries of each severity for each event in a group? That is, if I have an event group with some severities and some events related to it, I would expect to get a list of latest log entries for each combination of severity and event.

Answer 1

The most straight forward way would be something along the lines of:

groups = EventGroup.objects.all()
for group in groups:
    for event in group.event_set.all():
        LogEntry.objects.filter(event = event).latest('timestamp')

This will hit the database multiple times.

Another way would be to start with the LogEntry model. This approach requires fewer queries.

groups = EventGroup.objects.all()
for group in groups:
    LogEntry.objects.select_related().filter(event__group = group).latest('timestamp')

Warning: I haven't tested this code. This is working for me.