Django Conditional Statement Removes Overlap

Using Django Using the ORM
1

I am attempting to use the Conditional Statements When and Case to optimize my query to count the number of instances of of people with an age less than x.

The variable b is used to reference the number of instances that I am looking to capture for that particular age via b’s length.

Code:

    YEARS_PER_AGG = 5
    age_bins = [(i) for i in range(15, 85, YEARS_PER_AGG)]
    conditionals = [When(age__lte=bin, then=Value(f"{bin}")) for bin in age_bins]
    case = Case(*conditionals, output_field=CharField())

    age_groupings = person_salaries.values("education").annotate(age_less_than=case, count=Count("id"))
    b = PersonSalary.objects.filter(Q(education='1. < HS Grad') &  Q(age__lte=25))

output:

The length of b here is 33. This is the sum of where age_less_than is 20 and where age_less_than 25 below.

<QuerySet [{‘education’: ‘1. < HS Grad’, ‘age_less_than’: ‘20’, ‘count’: 10}, {‘education’: ‘1. < HS Grad’, ‘age_less_than’: ‘25’, ‘count’: 23}

What I am looking to create would be an output like this:

<QuerySet [{‘education’: ‘1. < HS Grad’, ‘age_less_than’: ‘20’, ‘count’: 10}, {‘education’: ‘1. < HS Grad’, ‘age_less_than’: ‘25’, ‘count’: 33}

To the best of my understanding the reason this is occurring is that each independent When statement is evaluated as as an elif rather than if.

2

Correct. From the docs for Case:

A Case() expression is like the if … elif … else statement in Python . Each condition in the provided When() objects is evaluated in order, until one evaluates to a truthful value. The result expression from the matching When() object is returned.

Note: This isn’t Django evaluating this. These are the semantics of the SQL CASE statement.

I believe that you would need to construct these as independent aggregates / annotations to get independent results.

2 Likes