10

I have a scenario where I want to annotate a queryset with externally prepared data in a dict. I want to do something like the following:

value_dict = {"model1": 123.4, "model2": 567.8}
qs = ModelClass.objects.annotate(
    value=Value(value_dict.get(F('model__code'), 0))
)

The results currently show all as 0 as the F() doesn't seem to be the best way to look up the dict seeing as it doesn't return a string and it is resolved further down the track.

Your help and suggestions would be much appreciated

I'm currently on Python 3.6 and Django 1.11

Artisan
  • 1,654
  • 16
  • 22

1 Answers1

28

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,654
  • 16
  • 22
  • `whens` is a list and you cannot do `**whens` because it is not a dictionary but a list. – user4426017 Apr 16 '18 at 18:58
  • 1
    You are correct @user4426017 that it is a list, however I am not expanding it with `**whens`, I am expanding it with `*whens` which is what is intended – Artisan Apr 17 '18 at 20:42
  • 1
    Building on what @Omar did, if your dictionary contains variables as key-values, use `then=Value(v)`. Worked for me. – Anomitra Jul 09 '18 at 13:08