Updating fields via subquery in Django

Question

I have an app with models and db schema that looks as shown below. I am trying to add field r to L2 in order to be able to access the related objects from model R. The new field is not shown in the schema figure.

Retrieving the desired value of field r using a subquery and an annotation works as expected. However, populating/updating the field with an update() call does not work. Do I have to modify my subquery? Or is this not possible at all in Django without resorting to raw SQL?

Models and schema

from django.db import models

class L1(models.Model):
    desc = models.CharField(max_length=16)
    m1 = models.ForeignKey('M1', on_delete=models.CASCADE)

class L2(models.Model):
    desc = models.CharField(max_length=16)
    l1 = models.ForeignKey('L1', on_delete=models.CASCADE)
    m2 = models.ForeignKey('M2', on_delete=models.CASCADE)

    # r is the field added
    r = models.ForeignKey('R', null=True, default=None, on_delete=models.SET_NULL)

class M1(models.Model):
    desc = models.CharField(max_length=16)

class M2(models.Model):
    desc = models.CharField(max_length=16)

class R(models.Model):
    desc = models.CharField(max_length=16)
    m1 = models.ForeignKey('M1', on_delete=models.CASCADE)
    m2 = models.ForeignKey('M2', on_delete=models.CASCADE)

Sample code

from random import randint
from django.db import connection, reset_queries
from django.db.models import F, OuterRef, Subquery
from myapp.models import L1, L2, M1, M2, R

# create random data
for m in range(10):
    M1.objects.create(desc=f'M1_{m:02d}')
    M2.objects.create(desc=f'M2_{m:02d}')
for r in range(40):
    R.objects.create(desc=f'R_{r:02d}', m1_id=randint(1,10), m2_id=randint(1,10))
for l1 in range(20):
    L1.objects.create(desc=f'L1_{l1:02d}',  m1_id=randint(1,10))
for l2 in range(100):
    L2.objects.create(desc=f'L2_{l2:02d}',  l1_id=randint(1,20), m2_id=randint(1,10))

# use subquery to annotate model - success
reset_queries()
subquery = Subquery(R.objects.filter(m2_id=OuterRef('m2_id'), m1_id=OuterRef('l1__m1_id')).values('id')[:1])
annotated = L2.objects.all().annotate(_r_id=subquery)
annotated_l=list(annotated)
print(connection.queries[-1])
# query SQL-1

# use subquery to annotate and update model - failure
reset_queries()
annotated.update(r_id=F('_r_id'))
# ...
# django.db.utils.ProgrammingError: missing FROM-clause entry for table "myapp_l1"
# LINE 1: ...ECT U0."id" FROM "myapp_r" U0 WHERE (U0."m1_id" = "myapp_l1"...
#                                                              ^
print(connection.queries[-1])
# produces SQL-2

SQL-1

SELECT
    "myapp_l2"."id",
    "myapp_l2"."desc",
    "myapp_l2"."l1_id",
    "myapp_l2"."m2_id",
    "myapp_l2"."r_id",
    (
        SELECT
            U0."id"
        FROM
            "myapp_r" U0
        WHERE (U0."m1_id" = "myapp_l1"."m1_id"
            AND U0."m2_id" = "myapp_l2"."m2_id")
    LIMIT 1) AS "_r_id"
FROM
    "myapp_l2"
    INNER JOIN "myapp_l1" ON ("myapp_l2"."l1_id" = "myapp_l1"."id")

SQL-2

UPDATE
    "myapp_l2"
SET
    "r_id" = (
        SELECT
            U0."id"
        FROM
            "myapp_r" U0
        WHERE (U0."m1_id" = "myapp_l1"."m1_id"
            AND U0."m2_id" = "myapp_l2"."m2_id")
    LIMIT 1)
WHERE
    "myapp_l2"."id" IN (
        SELECT
            V0."id"
        FROM
            "myapp_l2" V0
            INNER JOIN "myapp_l1" V1 ON (V0."l1_id" = V1."id"))

m000 · Answer 1 · 2021-11-12T18:10:24.413

The following finally did the trick. This was inspired by the answer here. Only, for this case, a nested subquery has to be used.

For the record, performance was pretty good. It took around 50sec to update 1.5M L2 objects from 830K L1 objects, and 12K R objects.

from django.db import connection, reset_queries
from django.db.models import OuterRef, Subquery
from myapp.models import L1, L2, M1, M2, R

# create queryset with annotation
subquery = Subquery(R.objects.filter(m2_id=OuterRef('m2_id'), m1_id=OuterRef('l1__m1_id')).values('id')[:1])
annotated = L2.objects.annotate(_r_id=subquery)

# use the queryset in a subquery to get the annotation value
reset_queries()
L2.objects.update(r_id=Subquery(annotated.filter(id=OuterRef('id')).values('_r_id')[:1]))
print(connection.queries[-1])
# produces SQL-good

# verify results with a loop
for l2 in L2.objects.all():
    r = R.objects.filter(m1_id=l2.l1.m1_id, m2=l2.m2_id).first()
    print(f'{str(r == l2.r):5s} {str(r):10s} {str(l2.r):10s}')

SQL-good

UPDATE
    "myapp_l2"
SET
    "r_id" = (
        SELECT
            (
                SELECT
                    U0."id"
                FROM
                    "myapp_r" U0
                WHERE (U0."m1_id" = V1."m1_id"
                    AND U0."m2_id" = V0."m2_id")
            LIMIT 1) AS "_r_id"
    FROM
        "myapp_l2" V0
        INNER JOIN "myapp_l1" V1 ON (V0."l1_id" = V1."id")
    WHERE
        V0."id" = "myapp_l2"."id"
    LIMIT 1) '