No content

● ●

No content

class Author(models.Model): name = models.CharField(max_length=200) def __str__(self): return self.name

class Genre(models.Model): name = models.CharField(max_length=200, unique=True) def __str__(self): return self.name

class Book(models.Model): title = models.CharField(max_length=200) author = models.ForeignKey( Author, on_delete=models.CASCADE, related_name="books" ) votes = models.PositiveIntegerField() genres = models.ManyToManyField( "Genre", related_name="books" ) def __str__(self): return self.title

No content

>>> from literature.models import Author, Book, Genre >>> Author.objects.count() 1142 >>> Book.objects.count() 3578 >>> Genre.objects.count() 412

No content

>>> Author.objects.get() Traceback (most recent call last): File "", line 1, in File ".../django/db/models/manager.py", line 82, in manager_method return getattr(self.get_queryset(), name)(*args, **kwargs) File ".../django/db/models/query.py", line 403, in get (self.model._meta.object_name, num) literature.models.Author.MultipleObjectsReturned: get() returned more than one Author -- it returned 1142!

>>> Author.objects.first()

>>> Author.objects.filter(name="Markus").first() >>> Author.objects.filter(name="Markus").get() Traceback (most recent call last): File "", line 1, in File ".../django/db/models/query.py", line 399, in get self.model._meta.object_name literature.models.Author.DoesNotExist: Author matching query does not exist.

No content

>>> Author.objects.filter(name__startswith="Lisa") , , ...]>

>>> Author.objects.filter(name__icontains="tom") , , , ...]>

No content

No content

>>> books = Book.objects.all() >>> for book in books: ... print(f"Title: {book.title} -- Author: {book.author.name}") Title: Harry Potter and the Sorcerer's Stone -- Author: J.K. Rowling Title: Harry Potter and the Prisoner of Azkaban -- Author: J.K. Rowling Title: A Short History of Nearly Everything -- Author: Bill Bryson Title: The Lord of the Rings -- Author: J.R.R. Tolkien Title: The Fellowship of the Ring -- Author: J.R.R. Tolkien Title: One Hundred Years of Solitude -- Author: Gabriel García Márquez Title: Angels & Demons -- Author: Dan Brown

No content

>>> from django.db import connection >>> connection.queries [{'sql': 'SELECT COUNT(*) AS "__count" FROM "literature_author"', 'time': '0.000'}, {'sql': 'SELECT COUNT(*) AS "__count" FROM "literature_book"', 'time': '0.000'}, ...]

No content

>>> books = Book.objects.select_related("author").all() >>> for book in books: ... print(f"Title: {book.title} -- Author: {book.author.name}") Title: Harry Potter and the Sorcerer's Stone -- Author: J.K. Rowling Title: Harry Potter and the Prisoner of Azkaban -- Author: J.K. Rowling Title: A Short History of Nearly Everything -- Author: Bill Bryson Title: The Lord of the Rings -- Author: J.R.R. Tolkien Title: The Fellowship of the Ring -- Author: J.R.R. Tolkien Title: One Hundred Years of Solitude -- Author: Gabriel García Márquez Title: Angels & Demons -- Author: Dan Brown

No content

>>> authors = Author.objects.all() >>> for author in authors: ... print(f"Author: {author.name}") ... for book in author.books.all(): ... print(f" - Title: {book.title}") Author: Bill Bryson - Title: A Short History of Nearly Everything Author: Edith Wharton - Title: Ethan Frome Author: Patricia A. McKillip - Title: In the Forests of Serre - Title: The Bards of Bone Plain

>>> authors = Author.objects.prefetch_related("books") >>> for author in authors: ... print(f"Author: {author.name}") ... for book in author.books.all(): ... print(f" - Title: {book.title}") Author: Bill Bryson - Title: A Short History of Nearly Everything Author: Edith Wharton - Title: Ethan Frome Author: Patricia A. McKillip - Title: In the Forests of Serre - Title: The Bards of Bone Plain

>>> books = Book.objects.filter(author_id__in=[...])

>>> from django.db.models import Prefetch >>> prefetch_qs = Book.objects.filter(title__startswith="H") >>> authors = Author.objects.prefetch_related( ... Prefetch("books", queryset=prefetch_qs) ... )

No content

COUNT

>>> from django.db.models import Count >>> authors = Author.objects.annotate(book_cnt=Count("books")) >>> for author in authors: ... print(f"Author: {author.name}: {author.book_cnt}") Author: Jen Wang: 10 Author: Sarah MacLean: 12 Author: Charles Soule: 12 Author: A.S. King: 1 Author: Jesmyn Ward: 1 Author: Victor LaValle: 2

SELECT "literature_author"."id", "literature_author"."name", COUNT("literature_book"."id") AS "book_cnt" FROM "literature_author" LEFT OUTER JOIN "literature_book" ON "literature_author"."id" = "literature_book"."author_id" GROUP BY "literature_author"."id", "literature_author"."name";

SUM

>>> from django.db.models import Sum >>> authors = Author.objects.annotate( ... sum_votes=Sum("books__votes") ... ).order_by("-sum_votes")[:5] >>> for author in authors: ... print(f"Author: {author.name}: {author.sum_votes} votes") Author: J.K. Rowling: 10354107 votes Author: Suzanne Collins: 10270371 votes Author: Rick Riordan: 5860434 votes Author: John Green: 5694398 votes Author: Stephen King: 5181285 votes

SELECT "literature_author"."id", "literature_author"."name", SUM("literature_book"."votes") AS "sum_votes" FROM "literature_author" LEFT OUTER JOIN "literature_book" ON "literature_author"."id" = "literature_book"."author_id" GROUP BY "literature_author"."id", "literature_author"."name" ORDER BY "sum_votes" DESC LIMIT 5

No content

>>> from django.db.models import CharField, Value >>> from django.db.models.functions import StrIndex, Substr >>> substr_exp = Substr("name", 1, ... StrIndex("name", Value(" ")) - Value(1), ... output_field=CharField(max_length=100)) >>> Author.objects.annotate(first_name=substr_exp).annotate( ... book_count=Count("books") ... ).order_by("-book_count") , , ...]>

>>> Author.objects.annotate( ... first_name=substr_exp ... ).values( ... "first_name" ... ).annotate( ... book_count=Count("books") ... ).order_by("-book_count")

No content

No content

No content

>>> books_qs = Book.objects.all() >>> authors = Author.objects.prefetch_related( ... Prefetch("books", queryset=books_qs) ... )

>>> books_qs = Book.objects.order_by("-votes") >>> authors = Author.objects.prefetch_related( ... Prefetch("books", queryset=books_qs) ... )

>>> books_qs = Book.objects.order_by("-votes")[:3] >>> list( ... Author.objects.prefetch_related( ... Prefetch("books", queryset=books_qs) ... ) ... ) Traceback (most recent call last): ... AssertionError: Cannot filter a query once a slice has been taken.

>>> from django.db.models import OuterRef, Subquery >>> book_sub_qs = Book.objects.filter( ... author_id=OuterRef("author_id") ... ).order_by("-votes").values_list("id", flat=True)[:3] >>> books_qs = Book.objects.filter( ... pk__in=Subquery(book_sub_qs) ... ).order_by("-votes")

>>> from django.db.models import OuterRef, Subquery >>> book_sub_qs = Book.objects.filter(author_id=OuterRef("author_id") ... ).order_by("-votes").values_list("id", flat=True)[:3] >>> books_qs = Book.objects.filter(pk__in=Subquery(book_sub_qs) ... ).order_by("-votes") >>> authors = Author.objects.prefetch_related( ... Prefetch("books", queryset=books_qs)) >>> for author in authors: ... print(f"Author: {author.name}") ... for book in author.books.all(): ... print(f" - {book.title}") Author: Bill Bryson - A Short History of Nearly Everything Author: Patricia A. McKillip - In the Forests of Serre - The Bards of Bone Plain

SELECT "literature_book"."id", "literature_book"."title", "literature_book"."author_id", "literature_book"."votes" FROM "literature_book" WHERE "literature_book"."id" IN ( SELECT U0."id" FROM "literature_book" U0 WHERE U0."author_id" = "literature_book"."author_id" ORDER BY U0."votes" DESC LIMIT 3 ) AND "literature_book"."author_id" IN (7, 16, ..., 18885860) ORDER BY "literature_book"."votes" DESC;

No content

>>> book_sub_qs = Book.objects.filter( ... genres=OuterRef("genres") ... ).order_by("-votes").values_list("pk", flat=True)[:3] >>> book_qs = Book.objects.distinct().filter( ... pk__in=Subquery(book_sub_qs)).order_by("-votes") >>> genres = Genre.objects.prefetch_related( ... Prefetch("books", queryset=book_qs)) >>> for genre in genres: ... print(f"Genre: {genre.name}") ... for book in genre.books.all(): ... print(f" - {book.title}")

SELECT DISTINCT "literature_book"."id", "literature_book"."title", "literature_book_genres"."genre_id" AS "_prefetch_related_val_genre_id", "literature_book"."author_id", "literature_book"."votes" FROM "literature_book" INNER JOIN "literature_book_genres" ON "literature_book"."id" = "literature_book_genres"."book_id" INNER JOIN "literature_book_genres" T4 ON "literature_book"."id" = T4."book_id" WHERE "literature_book"."id" IN ( SELECT U0."id" FROM "literature_book" U0 INNER JOIN "literature_book_genres" U1 ON U0."id" = U1."book_id" WHERE U1."genre_id" = "literature_book_genres"."genre_id" ORDER BY U0."votes" DESC LIMIT 3 ) AND T4."genre_id" IN (1, 2, ..., 411, 412) ORDER BY "literature_book"."votes" DESC

No content

No content