Django queryset for latest of each unique property value

Question

class SwallowMigration(models.Model):
    swallow = models.ForeignKey(Swallow)
    date = models.DateTimeField(auto_now_add=True)
    coconuts_carried = models.IntegerField()

How can I get the latest migration for each swallow?

Of course .latest() only gives me the very most recent entry. Is there a way (maybe using Aggregation with Max?) to get the latest for each instance of some value in another field?

Answer 1

If just having the Swallow instance and only the date of the most recent migration is enough, the following should do that for you:

Swallow.objects.annotate(latest_migration=Max('swallowmigration__date'))

Each Swallow instance from the resulting queryset will have a "latest_migration" attribute which would be the maximum datetime from the related objects.

If you do need the SwallowMigration instance, it is going to cost you N+1 queries, where N is the number of Swallow instances. Something like the following would do that for you, though you would have SwallowMigration objects with a prefetched Swallow instance on each instead of the other way around. It wouldn't be difficult to process the list and reverse them, setting the SwallowMigration instance as an attribute of the Swallow instance.

qs = SwallowMigration.objects.values(
    'swallow_id'
).annotate(
    latest_migration=Max('date')
)
migrations = []
for vals in qs:
    sm = SwallowMigration.objects.select_related(
        'swallow'
    ).get(
        swallow=vals['swallow_id'],
        date=vals['date'],
    )
    migrations.append(sm)

There may be (probably is) a way to return all of the data you want in a single query, but it would have to be raw SQL and you'd have to either use the data from that as is or construct model instances from it.

If this code is going to be executed frequently, it might be worth it to add a foreign key on Swallow to the latest SwallowMigration so that you can easily retrieve them in a single query using select_related(). You'd just have to make sure to keep that foreign key updated as new SwallowMigration instances are added.