Django Model: Getting result from a table using unique foreign key

user_12972377 0
Viewed 67

I have two models that look like this:

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

class Book(models.Model):
    author = models.ForeignKey('Author', on_delete=models.CASCADE)
    isbn_id = models.CharField(max_length=50, null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

How can I make a query such that:

  1. I get each author with the total number of books created, number of books with an isbn.

  2. Each time a book is created for an author, I can get the last_created, last_modified.

This is an example of the result I am trying to achieve;

s/n| author| no_books_with_isbn| all_books| last_created| last_modified
1.  Faye        2                   2       ...             ....
2.  Soya        2                   5
3.  Jake        6                   6
4.  Gurj        4                   4
5.  Zoom        2                   10
django-queryset django-models django sql
1 Answers

You need to annotate lots of aggregations to your queryset (Reference: Aggregation [Django Docs]). To get the counts you can use the Count [Django Docs] function and for the last_created / last_modified you can use the Max [Django Docs] function to achieve what you want:

from django.db.models import Count, Max, Q


queryset = Author.objects.annotate(
    all_books=Count('book'),
    no_books_with_isbn=Count(
        'book',
        filter=Q(book__isbn_id__isnull=False)
    ),
    last_created=Max('book_created_at'),
    last_modified=Max('book_updated_at')
)


for author in queryset:
    print(author.name, author.no_books_with_isbn, author.all_books, author.last_created, author.last_modified)
user_149918640
Related