Foreign Key Relaitonships and CRUD Operations

One-to-Many Relationships

# Create operation with direct foreign key reference
Book.objects.create(title='Analects', price=899.23, publisher_id=1)

# Create using related object
publisher_instance = Publisher.objects.get(id=2)
Book.objects.create(title='Dream of the Red Chamber', price=666.23, publisher=publisher_instance)

# Update foreign key reference
Book.objects.filter(id=1).update(publisher_id=2)

# Delete with cascade behavior
Publisher.objects.filter(id=1).delete()

Many-to-Many Relationships

# Add authors to book
book_instance = Book.objects.get(id=1)
book_instance.authors.add(1, 2, 3)  # Add by author IDs

# Add using author objects
author_objs = Author.objects.filter(id__in=[1, 2, 3])
book_instance.authors.add(*author_objs)

# Remove authors
book_instance.authors.remove(2)  # Remove by ID
book_instance.authors.remove(*author_objs)  # Remove by objects

# Replace all authors
book_instance.authors.set([4, 5])  # Set new author list

# Clear all author relationships
book_instance.authors.clear()

Query Direction Concepts

• Forward Query: Foreign key field exists on the querying model
• Reverse Query: Foreign key field exists on the related model

# Forward: Book -> Publisher (foreign key on Book)
book = Book.objects.get(id=1)
publisher = book.publisher

# Reverse: Publisher -> Book (no foreign key on Publisher)
publisher = Publisher.objects.get(id=1)
books = publisher.book_set.all()

Object-Based Cross-Table Queries

# Get publisher for specific book
book = Book.objects.get(id=1)
publisher = book.publisher

# Get all authors for a book
book = Book.objects.get(id=2)
authors = book.authors.all()

# Get author details from author
author = Author.objects.get(name='john')
details = author.author_detail

# Reverse query: books from publisher
publisher = Publisher.objects.get(name='East Press')
books = publisher.book_set.all()

# Reverse query: books from author
author = Author.objects.get(name='john')
books = author.book_set.all()

Double Underscroe Cross-Table Queries

# Join query: author details with author name
result = Author.objects.filter(name='john').values('author_detail__phone', 'name')

# Reverse join: same query
detail_result = AuthorDetail.objects.filter(author__name='john').values('phone', 'author__name')

# Multiple table joins
book_authors = Book.objects.filter(id=1).values('authors__name')

# Complex multi-table join
contact_info = Book.objects.filter(id=1).values('authors__author_detail__phone')

Aggregation Queries

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

# Single aggregation
average_price = Book.objects.aggregate(Avg('price'))

# Multiple aggregations
stats = Book.objects.aggregate(
    Max('price'), 
    Min('price'), 
    Sum('price'), 
    Count('id'), 
    Avg('price')
)

Grouping Queries

# Count authors per book
author_counts = Book.objects.annotate(
    author_count=Count('authors')
).values('title', 'author_count')

# Minimum price per publisher
min_prices = Publisher.objects.annotate(
    min_price=Min('book__price')
).values('name', 'min_price')

# Books with multiple authors
multi_author_books = Book.objects.annotate(
    author_count=Count('authors')
).filter(author_count__gt=1).values('title', 'author_count')

# Total book price per author
author_totals = Author.objects.annotate(
    total_price=Sum('book__price')
).values('name', 'total_price')

F() Expressions

from django.db.models import F
from django.db.models.functions import Concat
from django.db.models import Value

# Compare fields in same model
high_sales = Book.objects.filter(sales__gt=F('stock'))

# Update with field arithmetic
Book.objects.update(price=F('price') + 500)

# String concatenation with F expressions
Book.objects.update(title=Concat(F('title'), Value(' Bestseller')))

Q() Objects for Complex Filtering

from django.db.models import Q

# OR condition with Q objects
results = Book.objects.filter(Q(sales__gt=100) | Q(price__lt=600))

# AND condition
results = Book.objects.filter(Q(sales__gt=100), Q(price__lt=600))

# NOT condition
results = Book.objects.filter(~Q(sales__gt=100) | Q(price__lt=600))

# Dynamic Q object construction
dynamic_query = Q()
dynamic_query.connector = 'or'
dynamic_query.children.append(('sales__gt', 100))
dynamic_query.children.append(('price__lt', 600))
results = Book.objects.filter(dynamic_query)