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 ...
?
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.