Delete with related field query fails due to invalid SQL generation

[davidpurser]

Describe the bug
Using a filter query to select rows for deletion fails if the filter query is a related field query (i.e. across a foreign key).

To Reproduce

from tortoise import Model, fields, run_async
from tortoise.contrib.test import init_memory_sqlite


class Device(Model):
    device_id = fields.IntField(pk=True)
    name = fields.CharField(max_length=16)


class LogMessage(Model):
    device = fields.ForeignKeyField("models.Device", related_name="log_messages")
    date_created = fields.DatetimeField(auto_now_add=True)
    message = fields.CharField(max_length=1024)


@init_memory_sqlite
async def main() -> None:
    await LogMessage.filter(device__name="test").delete()


if __name__ == "__main__":
    run_async(main())

Expected behavior
In the example above, all "LogMessage" rows referencing devices having the name "test" should be deleted.

Instead, an invalid SQL DELETE query is generated.

[grigi]

Thanks for the detailed bug report. I'll add this as a test case (and fix) soon. (hopefully tomorrow)

[davidpurser]

I actually haven't had time to look into this any further, but it may require the ForeignKeyField to be nullable as well, and I should mention this happens with PostgreSQL specifically. In any case, a query like DELETE FROM LogMessage x LEFT JOIN Device y was generated, and as far as I can tell, it should be DELETE x FROM LogMessage x LEFT JOIN Device y or DELETE LogMessage FROM LogMessage LEFT JOIN Device (i.e., specify specifically which table is being deleted from). Hope that helps.

[grigi]

Standard DELETE FROM sql statement doesn't support LEFT JOIN, every DB has a different way of doing it.

🤔 (Why would you LEFT JOIN when deleting, then all relations becomes optional, and therefore the delete becomes practically unconditional)

Both MySQL and PostgreSQL supports DELETE FROM LogMessage USING Device WHERE LogMessage.fk = Device.pk

SQLite does not, a subqery is pretty much required, e.g. DELETE FROM LogMessage WHERE LogMessage fk IN (SELECT pk FROM Device)

[asitm9]

From Django delete method:

# The delete is actually 2 queries - one to find related objects,
# and one to delete. Make sure that the discovery of related
# objects is performed on the same database as the deletion.

We can think of a similar implementation here as well.

[asitm9]

@grigi I have done the fix for this one. Since it is my very first commit, I would like to get it reviewed from someone who has more experience it this project. After having the feedback will modify accordingly.

Right now I facing issues while creating PR, once that issue is resolved I will create a PR for the fix.

[lewoudar]

Hi guys, any news about the resolution of this issue?

[michael-sayapin]

Hello from 2025. It still happens.

[noy-solvin]

Hi! I've submitted PR #2139 that fixes this issue.

The root cause was that DELETE and UPDATE queries were using JOINs when filtering by related fields, which MySQL and SQLite don't support for those operations. The fix modifies DeleteQuery._make_query() and UpdateQuery._make_query() to use a subquery pattern instead (WHERE id IN (SELECT id FROM (...) AS _t)), and preserves LIMIT and ORDER BY clauses within the inner subquery.

Added test_delete_filter_with_foreign_key and test_update_filter_with_foreign_key to the test suite and all 1899 tests pass.

Would love a review whenever you get a chance. Happy to adjust anything! 🙏