Django高效查询实现：按分类获取归属对应目标的帖子

Hey there! Let's fix that inefficient two-step query and replace it with a single, optimized join-based solution that gets exactly the data you need without extra overhead.

The Problem with Your Current Approach #

Your current code makes two separate database queries: first fetching all Goal UUIDs for the category, then fetching Posts linked to those UUIDs. On top of that, you'd need to iterate through each Post to add the Goal title—this creates unnecessary database round-trips and Python-level processing that can slow things down, especially with large datasets.

The Optimized Solution #

We can use Django's ORM to perform a single joined query that directly filters Posts by their associated Goal's category, and pulls only the fields you need in one go. Here's how to rewrite your view:

from rest_framework.response import Response

@api_view(['GET'])
def get_most_recent_posts_by_category(request, category, count):
    # Perform a single joined query to fetch all required data
    raw_post_data = Post.objects.filter(
        # Traverse foreign key relationships: Post → Goal → GoalCategory
        goal_id__category=category
    ).values(
        # Extract exact fields across related tables
        'body',
        'goal_id__description',  # Pull the Goal's description (your "goal title")
        'created_date'  # Assuming AbstractBaseModel includes this creation date field
    ).order_by('-created_date')[:count]  # Optional: Sort by most recent, limit results

    # Format data to match your desired dictionary structure
    formatted_posts = [
        {
            'post_body': item['body'],
            'goal_title': item['goal_id__description'],
            'create_date': item['created_date']
        }
        for item in raw_post_data
    ]

    return Response(formatted_posts)

Why This Works Better #

1. Single Database Query: Django's ORM automatically generates a SQL JOIN for the Post → Goal → GoalCategory relationships, so all data is fetched in one trip to the database.
2. No Extra Iteration: We don't need to loop through Posts to add Goal titles—those values are pulled directly from the joined tables in the initial query.
3. Efficient Data Retrieval: Using values() means we only fetch the specific fields we need, not full model instances, which reduces memory usage and query time.
4. Built-in Sorting & Pagination: The order_by('-created_date')[:count] clause lets the database handle sorting and limiting results, which is far more efficient than doing it in Python.

Quick Verification Notes #

• Double-check the creation date field name: If your AbstractBaseModel uses created_at instead of created_date, update that in the values() call.
• The goal_id__category=category filter works because GoalCategory uses category as its primary key—Django automatically matches the string parameter to the related model's primary key.

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