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

0 comments

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

0 comments:

Post a Comment

Leave your thoughts ...
---
If you are having trouble with copy/pasting in comments, you need to sign in or click 'Preview'. For more information about this Firefox bug, see here.

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