Select the book with the least amount of pages for each author in a category

Question

These are my models:

class Author(models.Model):
    name = models.CharField()

class Category(models.Model):
    name = models.CharField()

class Book(models.Model):
    pages = IntegerField()
    author = ForeignKey(Author)
    category = ForeignKey(Category)

How do I select a book per author with the least amount of pages in a specific category? And then order by number of pages

If there are two books with the same amount of pages, select any of those. If a author doesn't have books in a category, that author can be ignored.

Thanks

Gasanov · Answer 1 · 2019-05-11T14:18:15.283

You can use following query:

books = Book.objects.values('author__name')\
            .filter(category=selected_category)\
            .annotate( min_pages=Min('pages'))\
            .order_by('min_pages')

Here's how it was tested:

tests.py

class SampleTestCase(TestCase):
    def setUp(self) -> None:
        a = Author.objects.create(name='a')
        b = Author.objects.create(name='b')
        c = Author.objects.create(name='c')  # Without a book in c1

        c1 = Category.objects.create(name='1')
        c2 = Category.objects.create(name='2')

        Book.objects.create(pages=20, author=a, category=c1)
        Book.objects.create(pages=10, author=a, category=c1)
        Book.objects.create(pages=15, author=a, category=c1)
        Book.objects.create(pages=10, author=a, category=c1)
        Book.objects.create(pages=10, author=b, category=c1)
        Book.objects.create(pages=5, author=b, category=c1)
        Book.objects.create(pages=7, author=b, category=c1)
        Book.objects.create(pages=1, author=a, category=c2)
        Book.objects.create(pages=1, author=b, category=c2)
        Book.objects.create(pages=1, author=c, category=c2)

    def test_queryset(self) -> None:
        selected_category = Category.objects.get(pk=1)
        books = Book.objects.values('author__name')\
            .filter(category=selected_category)\
            .annotate( min_pages=Min('pages'))\
            .order_by('min_pages')
        for book in books:
            print(book)
        # {'author__name': 'b', 'min_pages': 5}
        # {'author__name': 'a', 'min_pages': 10}

Update: If you need to get Book objects - not exactly sure, but I don't think it's possible to do in 1 query. Here's how you can do it in 2:

selected_category = Category.objects.get(pk=1)
ids = Book.objects.values('author__pk') \
    .filter(category=selected_category) \
    .annotate(min_pages=Min('pages')) \
    .annotate(id=Min('id'))\
    .values('id')\
    .order_by('min_pages')

books = Book.objects.filter(pk__in=ids)
for book in books:
    print(book)
# Book object (1)
# Book object (5)

Thanks, but this selects only the author name and the number of pages the book has. I need to get the Book objects or a way to select additional values. — Joseph Smith, May 11 '19 at 13:50
@RubenvanErk I've updated answer. I don't exactly like this, but can't figure out better solution. — Gasanov, May 11 '19 at 14:19
Doesn't this: `.annotate(id=Min('id'))` overwrite this `.annotate(min_pages=Min('pages'))`? — Joseph Smith, May 11 '19 at 21:11
@RubenvanErk no, you can chain annotations. First part is to add minimal page book, second is to group by authors. Resulted query will be `SELECT book.id, book.pages, book.author_id, book.category_id FROM book WHERE book.id IN (SELECT MIN(U0.id) AS id FROM book U0 WHERE U0.category_id = 1 GROUP BY U0.author_id)`, so even if it's 2 queries, they actually hit db only once. — Gasanov, May 12 '19 at 03:24

score 0 · Answer 2 · answered May 11 '19 at 13:36

You should use django's annotate() clause to determine the least amount of pages. And then you can add an order_by() to the QuerySet. Check out: https://docs.djangoproject.com/en/2.2/topics/db/aggregation/

try this:

x = Book.objects.all().values('author','category').annotate(pages = Sum('pages')).order_by('pages')

Select the book with the least amount of pages for each author in a category

2 Answers2