在Django中从多表获取数据：如何执行关联查询？

Hey there! Let’s work through your Django join query issue step by step. First, remember that Django’s ORM handles joins behind the scenes—you just need to make sure your model relationships are set up correctly, then use the right ORM methods to fetch related data.

If your models don’t have proper association fields (like ForeignKey, OneToOneField, or ManyToManyField), Django won’t know how to join your tables. Let’s use a simple example to illustrate:

# models.py
from django.db import models

# Parent model
class Author(models.Model):
    name = models.CharField(max_length=100)
    email = models.EmailField(unique=True)

# Child model (linked to Author via ForeignKey)
class Book(models.Model):
    title = models.CharField(max_length=200)
    # ForeignKey creates a many-to-one relationship (one author has many books)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, related_name="books")
    publication_year = models.IntegerField()

The related_name here lets you easily query all books by an author later (more on that below). If you skipped this field or used the wrong association type, your joins will fail.

Let’s cover the most frequent use cases and how to execute them correctly.

2.1 Fetching Main + Related Data (Forward Join) #

If you want to get all books along with their author details (this is a basic inner join), use select_related()—it tells Django to join the tables in a single query:

# views.py
from django.shortcuts import render
from .models import Book

def book_list(request):
    # select_related works for ForeignKey/OneToOneField (single related object)
    books_with_authors = Book.objects.select_related("author").all()
    
    # Now you can directly access author fields without extra database hits:
    # for book in books_with_authors:
    #     print(book.title, book.author.name)
    
    return render(request, "books/list.html", {"books": books_with_authors})

2.2 Reverse Join (Get Child Objects from Parent) #

To get all books written by a specific author, use the related_name we set earlier (or the default model_set if you didn’t define related_name):

def author_detail(request, author_id):
    # prefetch_related is for many-to-many or reverse ForeignKey relationships (multiple related objects)
    author = Author.objects.prefetch_related("books").get(id=author_id)
    
    # Access all books via author.books.all()
    return render(request, "authors/detail.html", {"author": author})

2.3 Filter Across Related Tables #

Need to filter results based on a field in a related table? Use double underscores (__) to traverse relationships:

# Get all books published after 2020 by authors named "Alice"
filtered_books = Book.objects.filter(
    author__name__icontains="Alice",
    publication_year__gt=2020
).select_related("author")

2.4 Join Multiple Tables #

If you have three or more linked tables, just chain select_related or prefetch_related for each relationship:

# Add a Publisher model linked to Book
class Publisher(models.Model):
    name = models.CharField(max_length=100)
    city = models.CharField(max_length=100)

class Book(models.Model):
    # ... existing fields ...
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

# Get all books from publishers in London, with their authors
london_books = Book.objects.select_related("author", "publisher").filter(publisher__city="London")

If your query still isn’t working, check these common mistakes:

• Missing association fields: Make sure you have a ForeignKey/ManyToManyField linking your tables. Without this, Django can’t join them.
• Typos in field names: When using __ to traverse relationships, double-check that the field names match exactly (e.g., author__name not author_name).
• Forgetting select_related/prefetch_related: Without these, you’ll hit the database multiple times (N+1 query problem) and might think data isn’t loading correctly.
• Incorrect reverse lookup: If you didn’t set related_name, use the default model_set (e.g., author.book_set.all() instead of author.books.all()).

Let’s put it all together with a real-world scenario:

# models.py
class Customer(models.Model):
    name = models.CharField(max_length=100)
    phone = models.CharField(max_length=20)

class Order(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name="orders")
    order_date = models.DateTimeField(auto_now_add=True)
    total = models.DecimalField(max_digits=10, decimal_places=2)

class OrderItem(models.Model):
    order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name="items")
    product = models.CharField(max_length=200)
    quantity = models.IntegerField()

# views.py
def order_summary(request):
    # Join Order with Customer (select_related) and prefetch OrderItem (prefetch_related)
    orders = Order.objects.select_related("customer").prefetch_related("items").all()
    return render(request, "orders/summary.html", {"orders": orders})

In your template, you can display the joined data like this:

{% for order in orders %}
    <div class="order">
        <h3>Order #{{ order.id }} - {{ order.customer.name }}</h3>
        <p>Date: {{ order.order_date|date:"F j, Y" }}</p>
        <p>Total: ${{ order.total }}</p>
        <h4>Items:</h4>
        <ul>
            {% for item in order.items.all %}
                <li>{{ item.product }} (x{{ item.quantity }})</li>
            {% endfor %}
        </ul>
    </div>
{% endfor %}

内容的提问来源于stack exchange，提问作者hemali savaliya