Order Django Queryset by attribute that could be char or int?

Question

I have the following model that I need to order by the grade attribute, but that attribute can be either a character or integer (K, KA, KF, NA, 1, 2, 3, etc.)... what is the best way to accomplish that? We are using MS SQL as a backend (I cannot use __regex because the DB version does not support it).

I annotated a first_letter_group attribute onto the queryset because any grade with multiple letters should be treated as it's first letter (KA = K, KN = K, etc.)

I'm trying to order these results by alpha first, then ascending numerically (e.g. 'P, K, 1, 2, 3, 4...')

# This is an unmanaged model
class Enrollment(models.Model):
    id = models.IntegerField(db_column="ID", primary_key=True)
    location_id = models.IntegerField(db_column="loc_id")
    grade = models.CharField(db_column="grade", max_length=10)
    end_year = models.IntegerField(db_column="end_year")
    run_date = models.DateField(db_column="run_date")
    student_count = models.IntegerField(db_column="students")

I've tried annotating additional fields where I cast the results to CharField or IntegerField but they (expectedly) error out when they hit something that does not convert.

    all_enrollments = (
        Enrollment.objects.filter(
            location_id__in=location_ids,
            end_year=today.year,
        )
        .order_by("-run_date")
        .annotate(first_letter_group=Substr("grade", 1, 1))
    )

    ordered_enrollment_list = (
        all_enrollments.annotate(
            number_field=Cast(
                "first_letter_group", output_field=IntegerField()
            ),
            str_field=Cast(
                "first_letter_group", output_field=IntegerField()
            ),
        )
        .order_by("-str_field", "number_field")
    )

I can't figure out how to do this without the __regex functionality which our DB does not support.

Answer 1

It's not pretty, but as far as i have understood your question it does the job

grades_list = (
    all_enrollments.values_list("first_letter_group", flat=True)
    .order_by("first_letter_group")
    .distinct()
    .values_list("first_letter_group", flat=True)
)  # List of only the integer grades
int_grades_list = list(
    map(
        lambda x: int(x),
        filter(lambda x: x.isdigit(), grades_list),
    )
)  # Gives a dynamic list of only ints: [1, 2, 3, 4, 5, 6, 8, 10, etc..]


ordered_enrollment_list = (
        all_enrollments.annotate(
            number_field=Case(
                  When(first_letter_group__in=int_grades_list, then=Cast(F('first_letter_group'), IntegerField())),
                  default=Value(0),
                  output_field=IntegerField()
               ),
            str_field=Case(
                  When(first_letter_group__in=int_grades_list, then=Value(False)),
                  default=Cast(F('first_letter_group'), CharField()),
                  output_field=CharField()
               ),
        )
        .order_by('-str_field', 'number_field')
    )