Less Obvious Things To Do With Django's ORM (PyCon SK 2019)

My talk from PyCon SK 2019

Speaker notes and examples: https://markusholtermann.eu/2019/03/less-obvious-things-to-do-with-djangos-orm/

Full code: https://github.com/MarkusH/talk-orm/

Transcript

1. View Slide

2. ●
   ●
   

   View Slide

3. View Slide

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

   View Slide

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

   View Slide

6. 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
   

   View Slide

7. View Slide

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

   View Slide

9. View Slide

10. >>> 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!
    

    View Slide

11. >>> Author.objects.first()
    
    

    View Slide

12. >>> 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.
    

    View Slide

13. View Slide

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

    View Slide

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

    View Slide

16. View Slide

17. View Slide

18. >>> 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
    

    View Slide

19. View Slide

20. >>> 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'}, ...]
    

    View Slide

21. View Slide

22. >>> 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
    

    View Slide

23. View Slide

24. >>> 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
    

    View Slide

25. >>> 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
    

    View Slide

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

    View Slide

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

    View Slide

28. View Slide

29. COUNT
    

    View Slide

30. >>> 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
    

    View Slide

31. 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";
    

    View Slide

32. SUM
    

    View Slide

33. >>> 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
    

    View Slide

34. 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
    

    View Slide

35. View Slide

36. >>> 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")
    , Meredith>, ...]>
    

    View Slide

37. >>> Author.objects.annotate(
    ... first_name=substr_exp
    ... ).values(
    ... "first_name"
    ... ).annotate(
    ... book_count=Count("books")
    ... ).order_by("-book_count")
    {'first_name': 'David', 'book_count': 53}, ...]>
    

    View Slide

38. View Slide

39. View Slide

40. View Slide

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

    View Slide

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

    View Slide

43. >>> 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.
    

    View Slide

44. >>> 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")
    

    View Slide

45. >>> 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
    

    View Slide

46. 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;
    

    View Slide

47. View Slide

48. >>> 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}")
    

    View Slide

49. 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
    

    View Slide

50. View Slide

51. View Slide