CODEWITHSUNDEEP
djangodjango-models
Alexander Allakhverdiyev
Alexander Allakhverdiyev

Reputation: 463

Django simple GROUP BY

I want to select one book per category and get count of books in each category

My model:

class Book(models.Model):
    title = models.CharField(max_length=100)
    category = models.CharField(max_length=100)

SQL should look something like: SELECT title, COUNT(*) as total FROM book GROUP BY category

I've tried to use annotate method

Book.objects.annotate(total_messages=Count('category'))

But it grouped by book.id, so I got list of all books, not grouped at all.

Upvotes: 1

Views: 335

Answers (3)

knbk
knbk

Reputation: 53649

From the MySQL documentation:

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.

This means that the only way to achieve what you want in a single query, is to use an OUTER JOIN statement. The Django ORM does not support OUTER JOIN statements from a table to itself, as far as I know.

The only way to get what you want (a list of books with the count of the number of books in that book's category), apart from using a raw SQL statement, is to use 2 separate queries and process them in Python.

qs = Book.objects.values('category').annotate(count=Count('id'))
count = dict([(x['category'], x['count']) for x in qs])
books = Book.objects.all()
for book in books:
    book.category_count = count['category']

Upvotes: 1

Hieu Nguyen
Hieu Nguyen

Reputation: 8623

Try this:

Book.objects.values('category').annotate(total_messages=Count('title'))

Upvotes: 1

alecxe
alecxe

Reputation: 473783

You should use values() if you want to specify grouping fields:

Book.objects.values('category').annotate(total_messages=Count('title'))

Here's the query it produces:

>>> Book.objects.values('category').annotate(total_messages=Count('title')).query.__str__()
Out[11]: u'SELECT "my_app_book"."category", COUNT("my_app_book"."title") AS "total_messages" FROM "my_app_book" GROUP BY "my_app_book"."category"'

Upvotes: 2

Related Questions