tortoise / tortoise-orm / 33453480034

84%

master: 94%

push

github

fix(queryset): support DELETE/UPDATE with filters on related fields (#2269)

Filters on related fields add JOINs to the query, but JOINs are not valid in
DELETE/UPDATE statements on most backends, so e.g. on SQLite

    LogMessage.filter(device__name="test").delete()

produces

    DELETE FROM "logmessage" LEFT OUTER JOIN "device" ...
    sqlite3.OperationalError: near "LEFT": syntax error

and the UPDATE path renders a broken aliased FROM clause on top of that.

When filters add joins, target the primary key through a subquery instead:

    DELETE FROM "logmessage" WHERE "id" IN (
        SELECT "_t"."id" FROM (
            SELECT "logmessage"."id" FROM "logmessage" LEFT OUTER JOIN ...
        ) AS "_t"
    )

The derived-table wrapper keeps MySQL (ER_UPDATE_TABLE_USED) happy, and the
non-JOIN path stays untouched.

Fixes #283

Co-authored-by: Mohammad <llllxyz@users.noreply.github.com>

3193 of 4240 branches covered (75.31%)

Branch coverage included in aggregate %.

21 of 22 new or added lines in 1 file covered. (95.45%)

11604 of 13277 relevant lines covered (87.4%)

0.87 hits per line