Django多模型多属性搜索：一对多关联查询失效问题

核心问题分析 #

• 模板遍历错误：遍历author_results时直接使用{{ book.title }}，但book变量未定义，也未从作者关联关系中获取书籍数据
• 未预取关联数据：查询作者时未预加载关联的Book和BookProfile，导致模板无法直接访问关联内容，还会引发N+1查询性能问题
• 模型关联冗余：BookProfile同时关联Author和Book，实际可通过Book间接关联作者，无需重复关联

代码修改方案 #

1. 修改views.py：预取作者关联的书籍数据 #

查询作者时用prefetch_related预加载关联的书籍及书籍详情，避免重复查询数据库：

from django.shortcuts import render
from .forms import SearchForm
from .models import Author, Book, BookProfile

def index(request):
   return render(request,'index.html')

def multiple_model_search(request):
    if request.method == 'GET':       
        form = SearchForm(request.GET)
        if form.is_valid():
            query = form.cleaned_data.get('query')
            # 预取作者关联的书籍及书籍详情
            author_results = Author.objects.filter(name__icontains=query).prefetch_related(
                'book_set',
                'book_set__bookprofile_set'
            )
            book_title = Book.objects.filter(title__icontains=query).select_related('author').prefetch_related('bookprofile_set')
            book_profile = BookProfile.objects.filter(isbn__icontains=query).select_related('author', 'booktitle')                    
           
            context = {
                'author_results': author_results,
                'book_title': book_title,
                'book_profile': book_profile,            
            }
            return render(request, 'search_results.html', context)
    else:
        form = SearchForm()
    return render(request, 'search_form.html', {'form': form})

2. 修改search_results.html：正确遍历作者的关联书籍 #

通过author.book_set访问作者的所有书籍，再通过书籍关联bookprofile_set获取ISBN和页数：

{% extends "base.html" %}

{% block content %}
  
 <h6>搜索结果：</h6>
  
    {% for author in author_results %}
        <li class="h4">{{ author.name }}</li>
        {% for book in author.book_set.all %}
            <li>书籍：{{ book.title }}</li>
            {% for profile in book.bookprofile_set.all %}
                <li>ISBN：{{ profile.isbn }} | 页数：{{ profile.page }}</li>
            {% endfor %}
        {% empty %}
            <li>该作者暂无书籍</li>
        {% endfor %}
        <hr>
    {% endfor %}
 
   {% for book in book_title  %}
   <li>{{book.title}}</li>
   <li>作者: {{book.author.name}}</li>
   {% for profile in book.bookprofile_set.all %}
        <li>ISBN：{{ profile.isbn }} | 页数：{{ profile.page }}</li>
   {% endfor %}
   <hr>
   {% endfor %}
   

      {% for profile in book_profile %}
      <li>书籍：{{ profile.booktitle.title }}</li>
      <li>作者: {{ profile.author.name }}</li>
      <li>ISBN：{{ profile.isbn }} | 页数：{{ profile.page }}</li>
      <hr>
    {% endfor %}

{% endblock  %}

3. 可选优化：简化模型关联（消除冗余） #

BookProfile无需直接关联Author，可通过booktitle间接获取作者，修改models.py：

from django.db import models

class Author(models.Model):
    name = models.CharField(max_length=100, blank=True, null=True)
    
    def __str__(self):
        return self.name

class Book(models.Model):
    title = models.CharField(max_length=100)
    author = models.ForeignKey(Author, on_delete=models.CASCADE, blank=True, null=True,)
    
    def __str__(self):
        return self.title

class BookProfile(models.Model):
    # 移除冗余的author关联，通过booktitle.author获取作者
    booktitle = models.ForeignKey(Book, on_delete=models.CASCADE, blank=True, null=True)
    isbn = models.CharField(max_length=100, blank=True, null=True)
    page = models.IntegerField(blank=True, null=True)
        
    def __str__(self):
        return self.isbn

注：若做此优化，模板中获取作者需改为profile.booktitle.author.name，views中的预取逻辑也需对应调整

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