Django QuerySet排序问题：无法实现预期自定义排序规则

Great question! Let's break down how to achieve your desired sorting behavior—you have two solid approaches here, and I'll walk you through both with code examples.

Why Your Initial order_by Didn't Work #

Your original code order_by("end_date", "primary", "-amount") fails for two key reasons:

• By default, Django sorts NULL values last when using ascending order on a date field, so your end_date: None records were getting pushed to the bottom instead of the top.
• You need distinct sorting rules within the two groups (end_date NULL vs non-NULL), which your single order_by chain didn't account for properly.

Approach 1: Single Query with annotate() (Recommended) #

This is the most efficient method since it runs a single database query. We'll add a temporary field to flag end_date NULL records as higher priority, then chain your sorting rules:

from django.db.models import Case, Value, IntegerField

queryset = MyModel.exclude_denied.filter(user=user).annotate(
    # Assign a priority value: 0 for NULL end_date (comes first), 1 otherwise
    end_date_priority=Case(
        When(end_date__isnull=True, then=Value(0)),
        default=Value(1),
        output_field=IntegerField(),
    )
).order_by(
    'end_date_priority',  # Step 1: Keep NULL end_date records at the top
    '-primary',           # Step 2: Within group, sort primary descending (True > False)
    '-amount',            # Step 3: Then sort amount descending
    '-end_date'           # Step 4: For non-NULL records, sort end_date newest to oldest
)

This will exactly match your expected order:

• All end_date: None records lead, sorted by primary then amount descending
• Followed by non-NULL records sorted from newest to oldest, with the same primary and amount sorting within each date group.

Approach 2: Split & Merge QuerySets #

If you prefer the split approach you considered, it's totally feasible. Just ensure each QuerySet is sorted individually before merging—Django preserves the order of each group when combining with |:

# First group: end_date is NULL, sorted by your rules
qs_null = MyModel.exclude_denied.filter(
    user=user,
    end_date__isnull=True
).order_by('-primary', '-amount')

# Second group: end_date is NOT NULL, sorted by your rules
qs_not_null = MyModel.exclude_denied.filter(
    user=user,
    end_date__isnull=False
).order_by('-end_date', '-primary', '-amount')

# Merge the two groups: qs_null stays at the front
final_queryset = qs_null | qs_not_null

Important Notes for This Method:

• This runs two separate database queries and combines results in memory—less efficient for large datasets compared to the single query approach.
• Don't apply an order_by() to the merged final_queryset—it will overwrite your custom group sorting.
• The two QuerySets must have identical field structures (which they do here, since they're from the same model with the same filter).

Which to Choose? #

• Use the annotate() method for better performance and cleaner code, especially with large datasets.
• Use the split/merge method if you need extra flexibility (e.g., adding additional logic to each group before combining).

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