Django: Model distinct() and order_by() not working nicely together

An interesting (and somewhat tedious) problem came up. We wanted to gather the most recently active blog authors sorted by post date.

Basically, it'd just be a distinct on the authors while sorting by latest post publication date.

In the words made famous by Jeremy Clarkson on Top Gear, "How hard can it be?". To be honest, quite hard.

Things were duplicated everywhere! After a few hours of tinkering, we found a solution.

from django.db.models.aggregates import Max

Author.objects.all_active_in_site().annotate(latest_published = Max('posts__published')).order_by('-latest_published')

It may look ridiculously simple, but it works!

Annotate sets "latest_published" to the date of the newest blog post (found using Max), removing duplications in the matching table.

Since it thinks there is only one blog entry per author, we don't get duplicates when joining.

Source

 
Copyright © Twig's Tech Tips
Theme by BloggerThemes & TopWPThemes Sponsored by iBlogtoBlog