Combining Django F, Value and a dict to annotate a queryset

Question

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 · Accepted Answer · 2018-01-16 14:11:44Z

39

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

edited Jan 16, 2018 at 14:11

answered Jan 10, 2018 at 12:41

Artisan

2,1542 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user4426017 Over a year ago

whens is a list and you cannot do **whens because it is not a dictionary but a list.

Artisan Over a year ago

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

Anomitra Over a year ago

Building on what @Omar did, if your dictionary contains variables as key-values, use then=Value(v). Worked for me.

Sumit Badsara Over a year ago

Nice workaround solution! But is it good enough to scale?

Skratt · Accepted Answer · 2023-03-23 12:26:42Z

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
        )}
    )

Collectives™ on Stack Overflow

Combining Django F, Value and a dict to annotate a queryset

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related