Django annotate field value from external dictionary

December 31, 2022

Lets say I have a following dict:

schools_dict = {
   '1': {'points': 10},
   '2': {'points': 14},
   '3': {'points': 5},
}

And how can I put these values into my queryset using annotate?
I would like to do smth like this, but its not working

schools = SchoolsExam.objects.all()
queryset = schools.annotate(
    total_point = schools_dict[F('school__school_id')]['points']
)

Models:

class SchoolsExam(Model):
    school = ForeignKey('School', on_delete=models.CASCADE),

class School(Model):
    school_id = CharField(),

This code gives me an error KeyError: F(school__school_id)

>Solution :

You can not work with F objects in a lookup, since a dictionary does not "understand" F-objects.

You can translate this to a conditional expression ^[Django-doc]:

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

schools = SchoolsExam.objects.annotate(
    total_point=Case(
        *[
            When(school__school_id=school_id, then=Value(v['points']))
            for school_id, v in school_dict.items()
        ]
    )
)

This will thus "unwind" the dictionary into CASE WHEN school_id=1 THEN 10 WHEN school_id=2 THEN 14 WHEN school_id=3 THEN 5.

However using data in a dictionary often does not make much sense: usually you store this in a table and perform a JOIN.