Allow update/delete with joined tables

[csirkeee]

The missing feature

Right now Diesel doesn't allow for update/delete with joined tables. This seems like an explicit decision based on having

pub trait IntoUpdateTarget: HasTable

Which means the update target must be a query that only touches one table.

But, using multiple tables in update/delete is a feature that both MySQL and Postgres support. (I didn't research the other databases.) So, it would be nice if Diesel could also support it. In this feature request I'll describe how that could look like.

Example code

Update

For example, working with the tables in the test suite, this could be a valid query:

update(
    comments::table
        .inner_join(posts::table.on(posts::id.eq(comments::post_id)))
        .inner_join(users::table.on(users::id.eq(posts::user_id)))
        .filter(users::id.eq(banned_id)))
    .set(comments::text.eq("Banned user"))
    .execute(&connection)
    .unwrap();

This would translate to the following query in MySQL:

UPDATE `comments`
	INNER JOIN `posts` ON `posts`.`id` = `comments`.`post_id`
	INNER JOIN `users` ON `users`.`id` = `posts`.`user_id` 
SET 
    `text` = ?
WHERE
    `users`.`id` = ?;

And into this query on Postgres:

UPDATE "comments"
SET 
    "text" = $1
FROM "posts"
    INNER JOIN "users" ON "users"."id" = "posts"."user_id"
WHERE
    "posts"."id" = "comments"."post_id"
    AND "users"."id" = $2;

Delete

Delete is a similar case:

delete(
    comments::table
        .inner_join(posts::table.on(posts::id.eq(comments::post_id)))
        .inner_join(users::table.on(users::id.eq(posts::user_id)))
        .filter(users::id.eq(banned_id)))
    .execute(&connection)
    .unwrap();

could translate, in MySQL, to:

DELETE FROM `comments`
USING `comments`
	INNER JOIN `posts` ON `posts`.`id` = `comments`.`post_id`
	INNER JOIN `users` ON `users`.`id` = `posts`.`user_id` 
WHERE
    `users`.`id` = ?;

and in Postgres:

DELETE FROM "comments"
USING "posts"
    INNER JOIN "users" ON "users"."id" = "posts"."user_id"
WHERE
    "posts"."id" = "comments"."post_id"
    AND "users"."id" = $1;

(I've checked all SQL to be valid on SQLFiddle.)

Implementation

The main problem with the implementation is that the query builder would need to handle join clauses differently from other filters. Also, the syntax is different for the databases, I don't know how much that complicates things. (It's possible that there exists a syntax that both MySQL and Postgres accept, but I haven't found one.)

Maybe the first part could be handled by using a different syntax in rust, like calling the joins on the update query and not on the table, like this:

update(comments::table.filter(users::id.eq(banned_id)))
    .inner_join(posts::table.on(posts::id.eq(comments::post_id)))
    .inner_join(users::table.on(users::id.eq(posts::user_id)))
    .set(comments::text.eq("Banned user"))
    .execute(&connection)
    .unwrap();