Django subquery with static VALUES expression

July 1, 2024

Is it possible using the Django ORM to write a subquery which SELECTs from a set of fixed values?

SELECT
  id,
  name,
  (
    SELECT
    new_ages.age
    FROM
    (VALUES ('homer', 35), ('marge', 34)) AS new_ages(name, age)
    WHERE new_ages.name = ages.name
  ) AS new_age
FROM
  ages
  ;

The output from such a query might be something like:

person_id  name    age
        1  homer   35
       42  marge   34
       99  bart    null

I know that the Django code would look something like this:

from django.db.models import OuterRef, Subquery
from my_app.models import Person

Person.objects.annotate(age=Subquery(...(name=OuterRef("name")))

But what goes in the ...?

>Solution :

As far as I know, you can’t define such "in-memory" tables in Django, or at least not right now.

The sad reality is that Django’s .bulk_update(…) implementation ^[Django-doc], which essentially is what you are doing here, has the same problem. It solves that in a bit of a "clumsy" way: a long CASE … WHEN … THEN … END sequence. We can do this the same way with:

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

new_ages = {'homer': 35, 'marge': 34}

Person.objects.annotate(
    age=Case(*[When(name=k, then=Value(v)) for k, v in new_ages.items()])
)

I wrote an article about this ^{[django-antipatterns]}, but again, it is not a good way to do this. Perhaps the best way would even be to define a model where one can dump items into, and then let the JOIN do the work.