Django Model Queries

In Django, one of the most powerful features is its Object-Relational Mapper (ORM) that allows you to interact with your database using Python code instead of writing raw SQL queries. This makes database operations more intuitive and helps maintain a clean separation between your application logic and database layer.

Introduction to Django ORM Queries

Django's ORM provides a simple, pythonic way to retrieve, create, update, and delete data from your database. When you define models in Django, the ORM automatically creates a query API that lets you create, retrieve, update, and delete objects using simple Python methods and operators.

Let's assume we have the following model for our examples:

python

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.CharField(max_length=100)
    publication_date = models.DateField()
    price = models.DecimalField(max_digits=6, decimal_places=2)
    pages = models.IntegerField()
    in_stock = models.BooleanField(default=True)
    
    def __str__(self):
        return self.title

Basic Queries

Retrieving All Objects

To retrieve all books from the database:

python

# Get all books
all_books = Book.objects.all()

This returns a QuerySet containing all the Book objects in the database.

Example output:

<QuerySet [<Book: Django for Beginners>, <Book: Python Crash Course>, <Book: Two Scoops of Django>]>

Filtering Objects

The filter() method lets you filter the results based on field values:

python

# Get all books by a specific author
django_books = Book.objects.filter(author="Daniel Roy Greenfeld")

# Get books published after 2020
recent_books = Book.objects.filter(publication_date__gt="2020-01-01")

# Get books that are in stock and cost less than $30
affordable_books = Book.objects.filter(in_stock=True, price__lt=30.00)

Getting a Single Object

To retrieve a single object, use the get() method:

python

# Get the book with a specific title
try:
    book = Book.objects.get(title="Django for Beginners")
    print(f"Found: {book.title} by {book.author}")
except Book.DoesNotExist:
    print("Book not found")
except Book.MultipleObjectsReturned:
    print("Multiple books found with that title")

It's important to catch the exceptions since get() will raise an error if the object doesn't exist or if multiple objects match the query.

Field Lookups

Django provides powerful field lookups that work like SQL's WHERE clauses. They're specified as keyword arguments to the filter(), exclude() and get() methods.

python

# Books with titles containing "Django"
django_books = Book.objects.filter(title__contains='Django')

# Case-insensitive title search
python_books = Book.objects.filter(title__icontains='python')

# Books published in 2021
books_2021 = Book.objects.filter(publication_date__year=2021)

# Books with more than 300 pages
thick_books = Book.objects.filter(pages__gt=300)

# Books with titles starting with "The"
the_books = Book.objects.filter(title__startswith='The')

Queries Across Relationships

Let's add another model to demonstrate relationship queries:

python

class Author(models.Model):
    name = models.CharField(max_length=100)
    birth_date = models.DateField(null=True, blank=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')
    # ... other fields remain the same

Now we can perform queries across relationships:

python

# Get all books by an author
author = Author.objects.get(name="J.K. Rowling")
author_books = author.books.all()  # Using the related_name

# Alternative way to get the same result
author_books = Book.objects.filter(author__name="J.K. Rowling")

# Get all authors who have written books with "Django" in the title
django_authors = Author.objects.filter(books__title__contains="Django")

QuerySet Methods

Chaining Filters

You can chain multiple filters to refine your queries:

python

# Get books published after 2020 that have "Python" in the title
books = Book.objects.filter(publication_date__gt="2020-01-01").filter(title__contains="Python")

# This is equivalent to:
books = Book.objects.filter(
    publication_date__gt="2020-01-01", 
    title__contains="Python"
)

Excluding Results

Use exclude() to exclude certain results:

python

# All books except those by "J.K. Rowling"
non_rowling_books = Book.objects.exclude(author__name="J.K. Rowling")

# Books with "Python" in the title but not "Beginners"
books = Book.objects.filter(title__contains="Python").exclude(title__contains="Beginners")

Limiting Results

To limit the number of returned results, use slicing:

python

# Get the first 5 books
first_five = Book.objects.all()[:5]

# Get books 5-10
books_5_to_10 = Book.objects.all()[5:10]

Ordering Results

Use order_by() to sort results:

python

# Order by publication date (oldest first)
books_by_date = Book.objects.order_by('publication_date')

# Order by publication date (newest first)
books_by_date_desc = Book.objects.order_by('-publication_date')

# Order by multiple fields (first by author name, then by title)
ordered_books = Book.objects.order_by('author__name', 'title')

Advanced Queries

Complex Filtering with Q Objects

For more complex queries involving OR operations, use Q objects:

python

from django.db.models import Q

# Books by "J.K. Rowling" OR with "Python" in the title
books = Book.objects.filter(Q(author__name="J.K. Rowling") | Q(title__contains="Python"))

# Books published after 2020 AND (by "J.K. Rowling" OR with "Python" in the title)
complex_query = Book.objects.filter(
    publication_date__gt="2020-01-01"
).filter(
    Q(author__name="J.K. Rowling") | Q(title__contains="Python")
)

Aggregation and Annotation

Use aggregation to calculate summary values:

python

from django.db.models import Avg, Max, Min, Count, Sum

# Get the average price of all books
avg_price = Book.objects.aggregate(Avg('price'))
# Output: {'price__avg': 29.99}

# Get multiple aggregates
aggregates = Book.objects.aggregate(
    avg_price=Avg('price'),
    max_price=Max('price'),
    min_price=Min('price'),
    total_books=Count('id')
)
# Output: {'avg_price': 29.99, 'max_price': 75.00, 'min_price': 9.99, 'total_books': 25}

Annotations add calculated fields to each object in the QuerySet:

python

from django.db.models import F

# Annotate each book with a discounted price (20% off)
books_with_discount = Book.objects.annotate(
    discounted_price=F('price') * 0.8
)

for book in books_with_discount[:3]:
    print(f"{book.title}: ${book.price} (Discounted: ${book.discounted_price})")

Using F() Expressions

F objects let you reference field values within the same model:

python

from django.db.models import F

# Update all book prices by increasing them by 10%
Book.objects.update(price=F('price') * 1.1)

# Find books where the number of pages is greater than 10x the price
expensive_books = Book.objects.filter(pages__gt=F('price') * 10)

Executing Raw SQL Queries

While Django's ORM is powerful, sometimes you might need to execute raw SQL:

python

# Execute a raw SQL query
books = Book.objects.raw('SELECT * FROM myapp_book WHERE pages > 500')

for book in books:
    print(book.title)

# With parameters to prevent SQL injection
books = Book.objects.raw(
    'SELECT * FROM myapp_book WHERE author_id = %s',
    [author.id]
)

Performance Optimization

Using select_related() and prefetch_related()

These methods help optimize queries with related models:

python

# Without select_related - generates a query for each book's author
books = Book.objects.all()
for book in books:
    print(f"{book.title} by {book.author.name}")  # Each access to book.author causes a new database query

# With select_related - performs a JOIN and fetches author data with the books
books = Book.objects.select_related('author')
for book in books:
    print(f"{book.title} by {book.author.name}")  # No additional queries

For many-to-many or reverse foreign key relationships, use prefetch_related:

python

# Efficiently fetch all authors and their books
authors = Author.objects.prefetch_related('books')
for author in authors:
    print(f"{author.name} wrote {author.books.count()} books:")
    for book in author.books.all():  # No additional queries
        print(f"- {book.title}")

Evaluating QuerySets

QuerySets are lazy - they don't hit the database until you actually need the data:

python

# This doesn't execute a query yet
q = Book.objects.filter(author__name="J.K. Rowling")

# The query executes when you:
# 1. Iterate over the QuerySet
for book in q:
    print(book.title)  # Query executes on first iteration
    
# 2. Slice the QuerySet with a step
book_list = q[0:10:2]  # Query executes here
    
# 3. Convert to a list or other data structure
book_list = list(q)  # Query executes here
    
# 4. Check if the QuerySet contains items
if q:  # Query executes here
    print("Found books")

Real-World Example

Let's put together what we've learned in a practical example. Imagine we're building a bookstore application and need to create a view that shows recommended books:

python

def recommended_books(request):
    # Get user's favorite genres from their profile
    user_genres = request.user.profile.favorite_genres.all()
    
    # Find books in those genres, published in the last 2 years,
    # that have good ratings and are in stock
    from django.utils import timezone
    two_years_ago = timezone.now().date() - timezone.timedelta(days=2*365)
    
    recommended = Book.objects.filter(
        genre__in=user_genres,
        publication_date__gte=two_years_ago,
        rating__gte=4.0,
        in_stock=True
    ).select_related('author').order_by('-rating')[:10]
    
    # Calculate discount prices
    books_with_prices = recommended.annotate(
        discounted_price=Case(
            When(publication_date__gte=timezone.now().date() - timezone.timedelta(days=90),
                 then=F('price') * 0.9),  # 10% off for new books
            default=F('price') * 0.85,  # 15% off for older books
            output_field=models.DecimalField()
        )
    )
    
    return render(request, 'bookstore/recommended.html', {
        'books': books_with_prices
    })

This example demonstrates:

1. Filtering by user preferences
2. Date-based filtering
3. Sorting by rating
4. Using select_related for efficiency
5. Using annotations to add calculated fields
6. Using Case/When for conditional logic

Summary

Django's ORM provides a powerful and intuitive way to interact with your database:

• Basic queries: all(), filter(), get(), and exclude()
• Field lookups like __contains, __gt, __startswith
• Relationship traversal with double underscores (author__name)
• Advanced filtering with Q objects for complex conditions
• Aggregations and annotations for calculations
• Performance optimization with select_related() and prefetch_related()

By mastering these query tools, you can build efficient and maintainable Django applications without having to write raw SQL in most cases.

Additional Resources and Exercises

Resources

• Django Documentation: Making Queries
• Django Documentation: QuerySet API Reference
• Django Documentation: Database Optimization

Exercises

1. Basic Filtering Practice: Create a model for a Product with fields like name, price, category, and in_stock. Write queries to:

   • Find all products under $50
   • Find all in-stock products in the "Electronics" category
   • Find products with "Phone" in their name

2. Relationship Query Challenge: Create models for Author, Book, and Category with appropriate relationships. Write queries to:

   • Find all books by authors born after 1980
   • Find authors who have written books in multiple categories
   • Find the category with the most books

3. Advanced Query Project: Build a small Django application for a movie database that allows users to search movies by:

   • Title, director, or actor
   • Release year range
   • Genre combinations (e.g., "Action AND Comedy")
   • Minimum rating

   Include sorting options and implement pagination for the results.

Happy querying!