Django Queryset with annotate

Question

I am writing one method in Django Manager model. I want to write method that finds out number of all sold copies (books) per author.

I have two models and method written in Manager. My problem is that method should also be chainable from any Author queryset, for example something like

Author.objects.filter(...).exlucde(...).total_copies_sold()

should also work.

Example:

author = Author.objects.create(...)
Book.objects.create(..., author=author, copies_sold=10)
Book.objects.create(..., author=author, copies_sold=20)

author_total_books = Author.objects.total_copies_sold().first()
>>> author_total_books.copies
30

Below my code. It works like in example above, but then I try something like:

author_books = Author.objects.filter(id=2).total_copies_sold()

I got

'QuerySet' object has no attribute 'annotate'

class AuthorManager(models.Manager):

    def total_copies_sold(self):
        return self.get_queryset().annotate(copies=Sum('book__copies_sold')



class Author(models.Model):
    first_name = models.CharField(max_length=120)
    last_name = models.CharField(max_length=120)

    objects = AuthorManager()


class Book(models.Model):
    title = models.CharField(max_length=120)
    copies_sold = models.PositiveIntegerField()
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')

[Edited]

Thank you schillingt for reply. I added:

class AuthorQueryset(models.QuerySet):
    def total_copies_sold(self):
        return self.annotate(copies=Sum('books__copies_sold'))

I tried something like:

author_books = Author.objects.filter(id=2).total_copies_sold()

>>> author_books.copies

I got

'AuthorQueryset' object has no attribute 'copies'

score 4 · Accepted Answer · answered Jan 29 '20 at 14:06

What you are lookig for is :

from django.db import models
from django.db.models import Sum
from django.db.models.functions import Coalesce


class AuthorManager(models.Manager):
    def get_queryset(self):
        return AuthorQuerySet(self.model, using=self._db)

    def annotate_with_copies_sold(self):
        return self.get_queryset().annotate_with_copies_sold()


class AuthorQuerySet(models.QuerySet):
    def annotate_with_copies_sold(self):
        return self.annotate(copies_sold=Sum('books__copies_sold'))


class Author(models.Model):
    objects = AuthorManager()
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)


class Book(models.Model):
    title = models.CharField(max_length=30)
    copies_sold = models.PositiveIntegerField()
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')

Now it is possible to chain queries e.g.:

author_total_books = Author.objects.total_copies_sold().first()

However you will no be able to use it on QuerySet object like:

author_books = Author.objects.filter(id=2).total_copies_sold()

That is because you are annotating Author object, not a QuerySet. To obtain that result you should execute:

Author.objects.annotate_with_copies_sold().get(id=2)
author.copies_sold 
15

score 3 · Answer 2 · answered Oct 16 '20 at 02:10

from django.db import models
from django.db.models import Sum
from django.db.models.functions import Coalesce

class AuthorManager(models.Manager):
    def get_queryset(self):
        return AuthorQuerySet(self.model, using=self._db)
    
    def annotate_with_copies_sold(self):
        return self.get_queryset().annotate_with_copies_sold()

class AuthorQuerySet(models.QuerySet):
    def annotate_with_copies_sold(self):
        # Write your solution here
        return self.annotate(copies_sold=Coalesce(Sum('books__copies_sold'), 0))


class Author(models.Model):
    # Make sure this manager is available.
    objects = AuthorManager()
    # objects = models.Manager.from_queryset(AuthorQuerySet)()
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)


class Book(models.Model):
    title = models.CharField(max_length=30)
    copies_sold = models.PositiveIntegerField()
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='books')
    enter code here

score 2 · Answer 3 · answered Jan 28 '20 at 17:31

2

You need to use Manager.from_queryset to set your manager. Here are the docs.

class AuthorQueryset(models.QuerySet):
    def total_copies_sold(self):
        ...

class Author(models.Model):
    objects = models.Manager.from_queryset(AuthorQueryset)()

answered Jan 28 '20 at 17:31

schillingt

13,493
2
32
34

score 0 · Answer 4 · answered Jun 09 '21 at 06:10

class AuthorManager(models.Manager):
    def get_queryset(self):
        return AuthorQuerySet(self.model, using=self._db)

    def annotate_with_copies_sold(self):
        return self.get_queryset().annotate_with_copies_sold()

class AuthorQuerySet(models.QuerySet):
    def annotate_with_copies_sold(self):
        return self.annotate(copies_sold=Sum('Book_Author__copies_sold'))


class Author(models.Model):
    first_name = models.CharField(max_length=50)
    last_name = models.CharField(max_length=50)
    objects = AuthorManager()

    class Meta:
        verbose_name_plural = "Author" 
        verbose_name = 'Author'
        ordering = ('first_name','last_name')

class Book(models.Model):
    title = models.CharField(max_length=250, unique=True)
    copies_sold = models.PositiveIntegerField(default=0)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name='Book_Author')

    class Meta:
        verbose_name_plural = "Book" 
        verbose_name = 'Book'
        ordering = ('title','copies_sold')




from vehicles.models import Author, Book
try:
    author  = Author.objects.get(first_name='Mark', last_name='Twain')
except Author.DoesNotExist:
    author  = Author.objects.create(first_name='Mark', last_name='Twain')

try:
    book = Book.objects.get(author=author, title='Adventrure of Huckleberry Finn', copies_sold=7)
except Book.DoesNotExist:
    book = Book.objects.create(author=author, title='Adventrure of Huckleberry Finn', copies_sold=7)
    pass
try:
    book = Book.objects.get(author=author, title='Adventrure of Tomm Saywer', copies_sold=4)
except Book.DoesNotExist:
    book = Book.objects.create(author=author, title='Adventrure of Tomm Saywer', copies_sold=4)
    pass

author = Author.objects.annotate_with_copies_sold().first()
print(author.copies_sold)
11

1.Create AuthorManager, AuthorQuerySet classes from Author and Books
2.Create Author, Book models 
3.Prepare Test Data and use model manager to filter the queryset

can you add some explanation instead of code dumping – StupidWolf Jun 09 '21 at 08:25 — StupidWolf, Jun 09 '21 at 08:25

Bhautik Jivani · Answer 5 · 2022-10-29T08:56:28.887

Count books sold by authors using Django ORM

Wihtout writing custom manager you can use "AuthorQueryset" in Author Manager

Just a menthion AuthorQueryset.as_manager() in Author models, that's it.

Here are the Django Docs

from django.db import models
from django.db.models import Sum, Value
from django.db.models.functions import Coalesce

# Create your models here.
class AuthorQuerySet(models.QuerySet):
    def annotate_with_copies_sold(self):
        return self.annotate(copies_sold=Coalesce(Sum('books__copies_sold'),Value(0)))

class Author(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)

    objects = AuthorQuerySet.as_manager()

class Book(models.Model):
    title = models.CharField(max_length=30)
    copies_sold = models.PositiveIntegerField()
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="books")

Django Queryset with annotate

5 Answers5