CODEWITHSUNDEEP
pythonsqldjango
Anton Barycheuski
Anton Barycheuski

Reputation: 720

How to rewrite Raw SQL Query (MySQL) with Django ORM query?

How to rewrite next Raw SQL Query (MySQL) with Django ORM query?

mysql> select author_id, 
              count(*) c 
        from library_books 
       group by author_id 
      having c>2 
       limit 3;

+---------------+----+

|     author_id | c  |

+---------------+----+

|             0 | 39 |

|          1552 | 17 |

|          1784 |  8 |

+---------------+-----

Upvotes: 0

Views: 670

Answers (1)

Alasdair
Alasdair

Reputation: 308799

First, annotate an author queryset with the number of books. 

from django.db.models import Count
authors = Author.objects.annotate(num_books=Count('librarybook')

You haven't shown your Django models, so I've had to guess that 'librarybook' is the correct name for the reverse relationship.

Then filter on the num_books to find authors with more than two books.

authors = Author.objects.annotate(num_books=Count('librarybook').filter(num_books__gt=2)

Finally, slice the queryset to limit it to 3 results.

authors = Author.objects.annotate(num_books=Count('librarybook').filter(num_books__gt=2)[:3]

You can then loop through the resulting authors and access the number of books.

for author in authors:
    print author.name, author.num_books

Upvotes: 2

Related Questions