This guide walks through defining related models and performing multi‑table queries, updates, and aggregations using Django’s ORM in an idiomatic way.

1. Model definitions

The sample data model for a library system uses three relationship types: ForeignKey (one‑to‑many), ManyToMany, and OneToOne.

from django.db import models

class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=64)
    email = models.EmailField()

class AuthorDetail(models.Model):
    GENDER_CHOICES = (
        (0, "female"),
        (1, "male"),
        (2, "secrecy"),
    )
    gender = models.SmallIntegerField(choices=GENDER_CHOICES)
    tel = models.CharField(max_length=32)
    addr = models.CharField(max_length=64)
    birthday = models.DateField()

class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.SmallIntegerField()
    au_detail = models.OneToOneField(AuthorDetail, on_delete=models.CASCADE)

class Book(models.Model):
    title = models.CharField(max_length=32)
    price = models.DecimalField(max_digits=5, decimal_places=2)
    pub_date = models.DateField()
    publish = models.ForeignKey(Publish, on_delete=models.CASCADE)
    authors = models.ManyToManyField(Author)

Apply migrations after setting up your DATABASES configuration.

2. Seed data (SQL snippets)

Insert a small dataset directly for experiments.

• Publish

INSERT INTO app01_publish(name, city, email) VALUES
  ("Huashan Publishing House", "Huashan Mountain", "hs@163.com"),
  ("Mingjiao Press", "Blackwood Cliff", "mj@163.com");

• AuthorDetail

INSERT INTO app01_authordetail(gender, tel, addr, birthday) VALUES
  (1, '13432335433', 'Huashan Mountain', '1994-05-23'),
  (1, '13943454554', 'Blackwood Cliff', '1961-08-13'),
  (0, '13878934322', 'Blackwood Cliff', '1996-05-20');

• Author

INSERT INTO app01_author(name, age, au_detail_id) VALUES
  ('linghu chong', 25, 1),
  ('ren woxing', 58, 2),
  ('Ren yingying', 23, 3);

3. One‑to‑many operations (Book → Publish)

Create a Book using either the object or the FK id.

from app01 import models

# Using a related object
press = models.Publish.objects.get(pk=1)
models.Book.objects.create(
    title="Dugu Nine Swords",
    price=180,
    pub_date="2018-10-23",
    publish=press,
)

# Using the foreign key id
models.Book.objects.create(
    title="Smiling Proud Wanderer",
    price=120,
    pub_date="2019-01-01",
    publish_id=1,
)

• book.publish is the related Publish object
• book.publish_id is the integer foreign key value

4. Many‑to‑many operations (Book ↔ Author)

Attach authors to a book by object or by id.

b = models.Book.objects.get(title="Dugu Nine Swords")
a1 = models.Author.objects.get(name="linghu chong")
a2 = models.Author.objects.get(name="Ren yingying")

# Add objects
b.authors.add(a1, a2)

# Add by primary keys
b.authors.add(a1.pk, a2.pk)

# Other common APIs
b.authors.remove(a1)        # or b.authors.remove(a1.pk)
b.authors.clear()           # remove all links
b.authors.set([a1, a2])     # replace existing with exactly these

5. Object‑based cross‑table access

5.1 One‑to‑many (Publish ↔ Book)

Forward via the FK field:

book = models.Book.objects.select_related("publish").get(pk=1)
city = book.publish.city

Reverse from the "one" side using the default related manager book_set:

press = models.Publish.objects.get(name="Mingjiao Press")
for bk in press.book_set.all():
    print(bk.title)

5.2 One‑to‑one (Author ↔ AuthorDetail)

Forward:

author = models.Author.objects.select_related("au_detail").get(name="linghu chong")
print(author.au_detail.tel)

Reverse:

for prof in models.AuthorDetail.objects.filter(addr="Blackwood Cliff"):
    print(prof.author.name)

5.3 Many‑to‑many (Author ↔ Book)

Forward from Book:

bk = models.Book.objects.get(title="Dugu Nine Swords")
for a in bk.authors.select_related("au_detail").all():
    print(a.name, a.au_detail.tel)

Reverse from Author:

auth = models.Author.objects.get(name="linghu chong")
for bk in auth.book_set.all():
    print(bk.title)

Related name customization

You can rename the reverse accessor using related_name. For example:

class Book(models.Model):
    # ...
    publish = models.ForeignKey("Publish", on_delete=models.CASCADE, related_name="books")

Then query reverse relations via books instead of book_set:

press = models.Publish.objects.get(name="Mingjiao Press")
for bk in press.books.all():
    print(bk.title)

6. Double‑undesrcore lookups across relatiosn

Django resolves JOINs when traversing relations using the double underscore syntax.

6.1 One‑to‑many lookups

# All book titles and prices from Mingjiao Press
models.Book.objects.filter(publish__name="Mingjiao Press").values_list("title", "price")

# Reverse: from Publish to Book
models.Publish.objects.filter(name="Mingjiao Press").values_list("book__title", "book__price")

6.2 Many‑to‑many lookups

# Titles written by 'linghu chong'
models.Book.objects.filter(authors__name="linghu chong").values_list("title", flat=True)

# Reverse: from Author to Book
models.Author.objects.filter(name="linghu chong").values_list("book__title", "book__price")

6.3 One‑to‑one lookups

# Forward: author → detail
a = models.Author.objects.filter(name="linghu chong").values("au_detail__tel")

# Reverse: detail → author
d = models.AuthorDetail.objects.filter(author__name="linghu chong").values("tel")

6.4 Multi‑hop examples

# Titles and author names for all books from Mingjiao Press
models.Book.objects.filter(publish__name="Mingjiao Press").values_list("title", "authors__name")

# Reverse: via Publish
a = models.Publish.objects.filter(name="Mingjiao Press").values_list(
    "book__title", "book__authors__age", "book__authors__name"
)

# Titles and publisher name for authors whose phone starts with 132
models.Book.objects.filter(authors__au_detail__tel__startswith="132").values_list(
    "title", "publish__name"
)

# Alternative via Author
models.Author.objects.filter(au_detail__tel__startswith="132").values(
    "book__title", "book__publish__name"
)

7. Aggregations and grouping

7.1 Simple aggregates

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

# Average price of all books
models.Book.objects.aggregate(avg_price=Avg("price"))

# Average, max, and min prices
models.Book.objects.aggregate(Avg("price"), Max("price"), Min("price"))

7.2 Grouping on a single table

Model:

class Emp(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    salary = models.DecimalField(max_digits=8, decimal_places=2)
    dep = models.CharField(max_length=32)
    province = models.CharField(max_length=32)

Sample data:

INSERT INTO app01_emp (id, name, age, salary, dep, province) VALUES
  (1, 'linghu chong', 24, 6000.00, 'Sales Department', 'Henan'),
  (2, 'Ren yingying', 18, 8000.00, 'Guangong Department', 'Guangdong'),
  (3, 'ren woxing', 56, 10000.00, 'Sales Department', 'Guangdong'),
  (4, 'Yue Lingshan', 19, 6000.00, 'Guangong Department', 'Henan'),
  (5, 'little dragon maiden', 20, 8000.00, 'Guangong Department', 'Hebei');

Queries:

from django.db.models import Count, Avg

# Department and headcount
models.Emp.objects.values("dep").annotate(emp_count=Count("id"))

# Department and average salary
models.Emp.objects.values("dep").annotate(avg_sal=Avg("salary"))

7.3 Grouping across related tables

Models:

class Dep(models.Model):
    title = models.CharField(max_length=32)

class Emps(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    salary = models.DecimalField(max_digits=8, decimal_places=2)
    dep = models.ForeignKey(Dep, on_delete=models.CASCADE)
    province = models.CharField(max_length=32)

Data:

INSERT INTO app01_dep (id, title) VALUES
  (1, 'Sales Department'),
  (2, 'Guangong Department');

INSERT INTO app01_emps (id, name, age, salary, province, dep_id) VALUES
  (2, 'linghu chong', 24, 8000.00, 'Henan', 1),
  (3, 'Ren yingying', 18, 9000.00, 'Guangdong', 2),
  (4, 'ren woxing', 57, 10000.00, 'Guangdong', 1),
  (5, 'Yue Lingshan', 19, 6000.00, 'Henan', 2),
  (6, 'little dragon maiden', 20, 8000.00, 'Hebei', 2);

Queries:

from django.db.models import Count, Avg

# Department title and headcount
models.Emps.objects.values("dep__title").annotate(emp_count=Count("id"))

# Department title and average salary
models.Emps.objects.values("dep__title").annotate(avg_sal=Avg("salary"))

The ORM performs the necessary JOINs behind the scenes and then applies GROUP BY on the chosen fields.

8. Practice queries

8.1 Cheapest book price per publisher

SELECT p.name, MIN(b.price)
FROM app01_publish AS p
LEFT JOIN app01_book AS b ON b.publish_id = p.id
GROUP BY p.name;

from django.db.models import Min

models.Publish.objects.values("name").annotate(min_price=Min("book__price"))

8.2 Number of authors per book

SELECT b.title, COUNT(a.name)
FROM app01_book AS b
LEFT JOIN app01_book_authors AS ba ON b.id = ba.book_id
LEFT JOIN app01_author AS a ON ba.author_id = a.id
GROUP BY b.id;

from django.db.models import Count

models.Book.objects.annotate(author_cnt=Count("authors__name")).values("title", "author_cnt")

8.3 Authors per book for titles starting with "Nine"

SELECT b.title, COUNT(a.name)
FROM app01_book AS b
LEFT JOIN app01_book_authors AS ba ON b.id = ba.book_id
LEFT JOIN app01_author AS a ON ba.author_id = a.id
WHERE b.title LIKE 'Nine%'
GROUP BY b.id;

models.Book.objects.filter(title__startswith="Nine") \
    .annotate(author_cnt=Count("authors__name")) \
    .values("title", "author_cnt")

8.4 Books written by more than one author

SELECT b.title, COUNT(a.name) AS num
FROM app01_book AS b
LEFT JOIN app01_book_authors AS ba ON b.id = ba.book_id
LEFT JOIN app01_author AS a ON a.id = ba.author_id
GROUP BY b.id
HAVING num > 1;

models.Book.objects.annotate(num_authors=Count("authors__name")) \
    .filter(num_authors__gt=1) \
    .values("title")

8.5 Order books by number of authors

SELECT b.title, COUNT(ba.author_id) AS num
FROM app01_book AS b
LEFT JOIN app01_book_authors AS ba ON b.id = ba.book_id
LEFT JOIN app01_author AS a ON a.id = ba.author_id
GROUP BY b.id
ORDER BY num;

models.Book.objects.annotate(num_authors=Count("authors__name")) \
    .order_by("num_authors") \
    .values("title", "num_authors")

8.6 Total price of books per author

SELECT a.name, SUM(b.price)
FROM app01_author AS a
LEFT JOIN app01_book_authors AS ba ON a.id = ba.author_id
LEFT JOIN app01_book AS b ON b.id = ba.book_id
GROUP BY a.id;

from django.db.models import Sum

models.Author.objects.annotate(total_price=Sum("book__price")).values("name", "total_price")

9. F expressions and Q objects

9.1 F expressions (field‑to‑field operations)

from django.db.models import F

# Employees with salary greater than their age
models.Emp.objects.filter(salary__gt=F("age"))

# Salary less than twice the age
models.Emp.objects.filter(salary__lt=F("age") * 2)

# Update: increment all book prices by 100
models.Book.objects.update(price=F("price") + 100)

9.2 Q objects (complex boolean logic)

from django.db.models import Q

# Price > 300 OR title starts with 'Nine'
models.Book.objects.filter(Q(price__gt=300) | Q(title__startswith="Nine"))

# Price > 300 OR NOT (published in March 2019)
models.Book.objects.filter(
    Q(price__gt=300) | ~Q(Q(pub_date__year=2019) & Q(pub_date__month=3))
)