CODEWITHSUNDEEP
djangofiltersql-order-bydjango-querysetdistinct
Megawatt
Megawatt

Reputation: 163

Get distinct django objects from queryset sorted by latest

I have this model (irrelevant fields hidden):

class Blog(models.Model):
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    is_published = models.BooleanField(default=False)
    published_date = models.DateTimeField(null=True, default=None, blank=True)

I only want to get the latest post of each author, sorted by published date.

I have tried the following in my view:

blog_list = Blog.objects.filter(
    is_published=True
).order_by('author', '-published_date').distinct('author')

Which works except this sorts all of the blog post in order of the author, not by published date.

The following would actually do the trick by switching the order by, but this throws an error in django:

blog_list = Blog.objects.filter(
    is_published=True
).order_by('-published_date', 'author').distinct('author')

I've checked all of the similar questions to this one and haven't encountered a solution that works. I think this request should be pretty simple but I don't see any way of achieving this.

Upvotes: 3

Views: 5013

Answers (5)

MohammadMahdi Aghajani
MohammadMahdi Aghajani

Reputation: 41

You can read this post in official Djang documentation. Based on this post, when you specify field names in distinct clause, you must provide an order_by() in the QuerySet, and the fields in order_by() must start with the fields in distinct(), in the same order. To get the goal you said above, following this :

from django.db.models import Max
blog_list = Blog.objects.filter(is_published=True).values('author').annotate(Max('published_date'))

Upvotes: 1

Gorkhali Khadka
Gorkhali Khadka

Reputation: 835

Try this code Now this is remove null values

from django.db.models import Max

Blog.objects.filter(pk__in=Blog.objects.order_by('-published_date').values(
        'author_id').annotate(max_id=Max('pk')).values('max_id'),
                        is_published=True, author_id__isnull=False).order_by('-author_id')

Upvotes: 0

Megawatt
Megawatt

Reputation: 163

I was able to get this to work using the following:

blog_ids = Blog.objects.filter(is_published = True)\
.order_by('author_id', '-published_date')\
.distinct('author_id')\
.values('id', flat=True)

blog_list = Blog.objects.filter(id__in=blog_ids)\
.order_by('-published_date')

Which gets exactly what I wanted!!!!

More detail on this method can be found here: https://stackoverflow.com/a/32760239/2990550

Upvotes: 5

anjaneyulubatta505
anjaneyulubatta505

Reputation: 11665

Try this solution

blog_list = Blog.objects.filter(is_published=True).distinct('author').order_by('-published_date', 'author')

Upvotes: 0

Ashish
Ashish

Reputation: 459

I think you should try this:

Blog.objects.filter(is_published=True).order_by('-published_date').order_by('author').distinct('author')

It will give what you want. here you will get all authors latest blogs.

Upvotes: 0

Related Questions