Advanced ordering with django ORM

user_7931950 0
Viewed 183

With the following model (it is just an example for the sake of explanation)

class Publisher(models.Model):
    name = models.CharField(max_length=300)

class Book(models.Model):
    name = models.CharField(max_length=300)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE, related_name='books')

I would like to list all the publishers and orders them first by "whether they have at least one book published" and then by their name

The closest I came up to is the following request

Publisher.objects.all().annotate(nb_books=Count('books')).order_by('-nb_books', 'name').distinct()

Unfortunately it is not exactly what I need since it will display all the publishers that have 5 books then all the ones that have 4 books then all the ones that have 3 published books etc.

What I would need is to have all the publishers that have at least one book published in one bloc and then the ones that don't have any

Is there a way I can achieve that ?

django-orm django python
1 Answers

You can work with an Exists subquery [Django-doc]:

from django.db.models import Exists, OuterRef

has_book = Book.objects.filter(
    publisher_id=OuterRef('pk')
)

Publisher.objects.annotate(
    nb_books=Exists(has_book)
).order_by('-nb_books', 'name')
user_675790
Related