Support delete with join `DELETE alias FROM tbl alias JOIN other_tbl ...`

[LeoniePhiline]

trafficstars

Motivation

A MariaDB/MySQL query like:

DELETE `activity` FROM `activity`
LEFT JOIN `subscription`
    ON `subscription`.`uid` = `activity`.`subscription`
WHERE 
  ...;

Proposed Solutions

DeleteStatement supports only from_table (https://docs.rs/sea-query/latest/sea_query/query/struct.DeleteStatement.html#method.from_table) but there is no support for stating from which table rows should be deleted when there are multiple tables involved in the query.

I propose to add DeleteStatement::table(table_ref) (or similarly named) to build the above query.

I also propose to add join support to DeleteStatement.

Additional Information

Note that DELETE FROM tbl LEFT JOIN other_tbl is a syntax error, as with joins "delete from which table" must is part of the syntax; i.e. DELETE tbl FROM tbl LEFT JOIN other_tbl.

This feature request for DELETE ... FROM ... JOIN ... is related to https://github.com/SeaQL/sea-query/issues/608 - a feature request for UPDATE TABLE ... JOIN ... SET ....

[cdhowie]

I think the biggest trick will be implementing this across all databases, because the generated SQL can have a different structure. For example, in MySQL we have

DELETE a FROM a INNER JOIN b ON ...

But in PostgreSQL we have

DELETE FROM a USING b WHERE ...

It may be a challenge to implement this across all query builders for this reason, especially when using both an ON and a WHERE clause in a single query.