[Django]-Combining Django F, Value and a dict to annotate a queryset

by

38👍

✅

The closest I’ve come so far is to loop through the dict and union a whole bunch of filtered query sets based on the dict keys.
Like so:

value_dict = {"model1": 123.4, "model2": 567.8}
array_of_qs = []
for k, v in value_dict.items():
    array_of_qs.append(
        ModelClass.objects.filter(model__code=k).annotate(value=Value(v))
    )
qs = array_of_qs[0].union(*array_of_qs[1:])

The qs will then have the result that I want, which is the values of the dict based on the keys of the dict annotated to each instance.

The beauty of this is that there is only one call to the db which is when I decide to use qs, other than that this whole process doesn’t touch the DB from what I understand.

Not sure there is a better way yet but will wait to see if there are other responses before accepting this one.

NEW IMPROVED WORKING METHOD BELOW

Unfortunately the above doesn’t work with values_list or values as of this current version 1.11 of Django due to this issue:
https://code.djangoproject.com/ticket/28900

I devised a cleaner way below:

value_dict = {"model1": 123.4, "model2": 567.8}
whens = [
    When(model__code=k, then=v) for k, v in value_dict.items()
]
qs = ModelClass.objects.all().annotate(
    value=Case(
        *whens,
        default=0,
        output_field=DecimalField()
    )
)

Hope it helps someone

👤Artisan

1👍

Not being able to do that has annoyed me for years.

Based on the accepted answer, I’ve designed a generic QuerySet :

def add_from_dict_value(self, reference_dict, fieldname_used_as_key, annotation_name_to_generate, output_field=CharField(), default=None)->:
    """
    enables annotations with values coming from a dictionary

    Use:
        def add_highlight(self)->models.QuerySet:
            return self.add_from_dict_value(MY_CONVERSION_DICTIONARY, 'rating', 'highlight')
    """ 

    whens = [
        When(**{fieldname_used_as_key:k, 'then':Value(v)}) for k, v in reference_dict.items()
    ]
    return self.annotate(**{
        annotation_name_to_generate : Case(
            *whens,
            default=default,
            output_field=output_field
        )}
    )
👤Skratt

Leave a comment