Django model queryset

Prerequisites

QuerySet

A QuerySet is a collection of database queries that return data from your Django models. Think of it as a "lazy" representation of a database query. When you define a QuerySet, the query isn’t executed until the data is actually needed.

Example:

from myapp.models import Book

# QuerySet is defined but not executed
books = Book.objects.all()

# Query is executed when data is accessed
for book in books:
    print(book.title)

Creating a QuerySet

QuerySets are created by accessing the objects attribute of a model. The most common QuerySet methods include:

  1. all(): Returns all records.
  2. filter(): Filters records based on conditions.
  3. exclude(): Excludes records that match a condition.
  4. get(): Returns a single, specific object.

Example:

from myapp.models import Book

# Fetch all books
books = Book.objects.all()

# Fetch books with a specific title
filtered_books = Book.objects.filter(title="Django for Beginners")

# Exclude books authored by "Anonymous"
non_anonymous_books = Book.objects.exclude(author="Anonymous")

# Get a single book by primary key
specific_book = Book.objects.get(id=1)

Lazy Evaluation of QuerySets

Django QuerySets are lazy. They are not executed until their data is actually needed. This means you can chain methods without hitting the database multiple times.

Example:

# Query is not executed
books = Book.objects.filter(published=True)

# Query executes here
count = books.count()

Common QuerySet Methods

1. filter()

Filters the records using conditions.

from myapp.models import Author

# Filter authors with more than 5 published books
authors = Author.objects.filter(books_published__gt=5)

2. exclude()

Excludes records matching certain conditions.

# Exclude authors who are inactive
active_authors = Author.objects.exclude(is_active=False)

3. order_by()

Orders results by one or more fields.

# Order books by publication date, descending
books = Book.objects.order_by('-publication_date')

4. distinct()

Removes duplicate rows.

# Fetch distinct genres
genres = Book.objects.values('genre').distinct()

5. values() and values_list()

Returns dictionaries or tuples of field values.

# Get dictionaries of book titles and authors
book_data = Book.objects.values('title', 'author')

# Get list of titles
titles = Book.objects.values_list('title', flat=True)

6. count()

Returns the number of records.

# Count all published books
published_count = Book.objects.filter(published=True).count()

7. first() and last()

Fetches the first or last record.

# Get the first published book
first_book = Book.objects.filter(published=True).first()

8. exists()

Checks if any records exist.

# Check if any unpublished books exist
unpublished_exists = Book.objects.filter(published=False).exists()

Advanced QuerySet Features

1. Chaining QuerySet Methods

QuerySet methods can be chained for complex queries.

# Get published books by a specific author
books = Book.objects.filter(published=True).filter(author="John Doe")

2. Annotations

Annotations allow you to add calculated fields to your QuerySet using the annotate() method.

from django.db.models import Count

# Count the number of books for each author
author_book_count = Author.objects.annotate(num_books=Count('book'))

3. Aggregations

Aggregations perform calculations across QuerySets.

from django.db.models import Avg

# Calculate average book price
average_price = Book.objects.aggregate(Avg('price'))

4. Raw SQL

If QuerySet methods are insufficient, raw SQL queries can be used.

# Use raw SQL to fetch books
books = Book.objects.raw('SELECT * FROM myapp_book WHERE published = True')

Performance Considerations

1. QuerySet Caching

QuerySets are evaluated only once. If reused, re-fetching data may incur performance costs. To cache results:

# Cache QuerySet results
books = list(Book.objects.all())

Use select_related to optimize foreign key lookups.

# Fetch books along with their related authors
books = Book.objects.select_related('author')

Use prefetch_related for many-to-many and reverse foreign key relationships.

# Prefetch related genres
books = Book.objects.prefetch_related('genres')

Examples of Complex Queries

1. Fetch books published in the last year:

from django.utils.timezone import now
from datetime import timedelta

last_year = now() - timedelta(days=365)
recent_books = Book.objects.filter(publication_date__gte=last_year)

2. Fetch authors with more than 10 books:

from django.db.models import Count

prolific_authors = Author.objects.annotate(book_count=Count('book')).filter(book_count__gt=10)

3. Fetch books in multiple genres:

genres = ['Fiction', 'Fantasy']
books = Book.objects.filter(genre__in=genres)

References: