Django ORM按认证角色动态统计用户已完成案例数的方案问询

Yes, you can absolutely achieve this with Django ORM! The issue with your current code is that Sum doesn’t accept a list/generator of Count aggregates—instead, we need to either combine all your conditions into a single Count with a filtered Q object, or use subqueries to count per course and sum those up.

Solution 1: Combined Filter with Single Count (Preferred) #

This is the most efficient approach, as it uses a single aggregate query. Here’s how to implement it:

1. Build a combined Q filter that links each course to its eligible roles. For each course in your filter, we add a condition that checks if the case belongs to the course AND the user has any of the eligible roles for that course.
2. Use Count with this filter and add distinct=True to avoid overcounting cases if a user is in multiple eligible roles.

from django.db.models import Q, Count, IntegerField

# Get your course list and role filters from the helper function
course_list, role_filter = convert_filter_str(input_filter_str)

# Build the combined filter
combined_filter = Q()
for course, roles in zip(course_list, role_filter):
    # Match cases of this course where the user has eligible roles
    course_role_condition = Q(patientcase__course_type=course) & Q(groups__name__in=roles)
    combined_filter |= course_role_condition

# Annotate the User QuerySet with the total completed cases
final_qs = user.annotate(
    completed_cases=Count(
        'patientcase',
        filter=combined_filter,
        distinct=True,
        output_field=IntegerField()
    )
)

Why this works:

• The combined_filter ORs together all the (course + role) conditions, so we count only cases that match both the course type and the user's eligible roles for that course.
• distinct=True ensures we don't count the same case multiple times if a user is in multiple eligible roles (since the many-to-many join with Group would otherwise create duplicate rows).

Solution 2: Subqueries for Per-Course Counts (Alternative) #

If you need more granular control (e.g., if your case-course relationship is complex), you can use subqueries to count cases per course and sum the results. We use Coalesce to handle cases where a user has no matching cases for a course (to avoid NULL values):

from django.db.models import Subquery, OuterRef, Count, IntegerField, Coalesce

course_list, role_filter = convert_filter_str(input_filter_str)

# Initialize total cases to 0
total_cases = 0

for course, roles in zip(course_list, role_filter):
    # Subquery to count cases for this course and eligible roles
    course_case_count = Coalesce(
        Subquery(
            PatientCase.objects.filter(
                user=OuterRef('pk'),
                course_type=course
            ).filter(
                user__groups__name__in=roles
            ).annotate(cnt=Count('pk')).values('cnt')[:1],
            output_field=IntegerField()
        ),
        0,
        output_field=IntegerField()
    )
    total_cases += course_case_count

# Annotate the total
final_qs = user.annotate(completed_cases=total_cases)

Why this works:

• Each subquery calculates the number of cases for a specific course where the user has eligible roles.
• Coalesce converts NULL (when there are no matching cases) to 0, so adding subqueries doesn't result in a NULL total.
• Summing the subqueries gives the total number of completed cases across all filtered courses.

Key Notes: #

• Adjust patientcase__course_type to match your actual model field (e.g., patientcase__course__name if you have a Course foreign key).
• The distinct=True in Solution 1 is crucial to prevent overcounting due to the many-to-many relationship between User and Group.

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