Django单查询实现按指定评分列表顺序各取10条Property数据

Great question! Handling this efficiently with a single query is totally doable using Django's ORM, leveraging window functions and conditional annotations to meet all three requirements. Here's how to implement it:

Step 1: Map Ratings to Priority Order #

First, we need to enforce your exact rating_list order by assigning a numerical priority to each rating (including the "unrated" case, which maps to rating IS NULL). This ensures our final results are sorted correctly.

Step 2: Use Window Functions to Number Rows per Rating #

We'll use the RowNumber() window function to assign a unique number to each row within its rating group. This lets us filter to only the first 10 rows per rating.

Full Implementation Code #

from django.db.models import Case, When, Value, IntegerField, Q
from django.db.models.functions import Window, RowNumber

# Your target rating list (including unrated)
rating_list = [5, 4, 1, 2, 3, "unrated"]

# Create a conditional annotation to define sort priority matching your list order
order_priority = Case(
    When(rating=5, then=Value(1)),
    When(rating=4, then=Value(2)),
    When(rating=1, then=Value(3)),
    When(rating=2, then=Value(4)),
    When(rating=3, then=Value(5)),
    When(rating__isnull=True, then=Value(6)),  # Handle unrated
    output_field=IntegerField(),
)

# Define a window function to number rows within each rating group
row_number = Window(
    partition_by="rating",  # Group rows by their rating value
    order_by="id"  # Use any field you want to sort within the rating group (e.g., '-created_at' for newest first)
)

# Execute the single query
filtered_properties = Property.objects.annotate(
    sort_priority=order_priority,
    row_num=row_number
).filter(
    # Filter to only the ratings in your list (including unrated)
    Q(rating__in=[5, 4, 1, 2, 3]) | Q(rating__isnull=True),
    row_num__lte=10  # Keep only the first 10 rows per rating
).order_by(
    "sort_priority", "row_num"  # Sort first by your specified order, then by row number
)[:60]  # Optional: Limit to exact 60 rows (6 ratings × 10 each)

Why This Works #

• Single Query: All logic is embedded in one ORM query, which translates to a single SQL hit to your database—critical for performance with large datasets.
• 10 Rows per Rating: The row_num__lte=10 filter ensures we only keep the first 10 entries from each rating group (as numbered by the window function).
• Strict Ordering: The sort_priority annotation assigns a unique value to each rating matching your rating_list sequence, so the final order_by clause guarantees results are returned in your desired order.

Notes for Optimization #

• Add a database index on the rating field if you haven't already—this will speed up the window function's partitioning and filtering.
• Adjust the order_by parameter in the Window function to match your needs (e.g., use -updated_at if you want the most recently updated entries per rating).

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