sql - 如何使用Diesel执行 `UPDATE FROM`？

Question

假设我有三个表a，b和c:

create table c (
  id serial primary key,
  can_edit_b boolean not null
);
create table b (
  id serial primary key,
  value text not null
);
create table a (
  id serial primary key,
  c_id integer not null references c(id),
  b_id integer not null references b(id)
);

我想更新 b(为 c实例指定一个ID)，只要该 c实例由 a实例引用也为 b且 c.can_edit_b为true即可。我想做什么的SQL:

update b
set value = "some value"
from c, a
where a.b_id == b.id
where a.c_id == <user id (inserted as a Rust i32)>
where c.can_edit_b == true

我找不到对应于SQL from的 relevant method/function in Diesel's API。如果我尝试使用 inner_join，那么编译器会告诉我 inner_join尚未定义 UpdateStatement。

Answer 1

您可以联接表，应用过滤器，然后将其用作更新条件:

#[macro_use]
extern crate diesel; // 1.4.5, features = ["postgres"]

use diesel::prelude::*;

table! {
    a {
        id -> Integer,
        c_id -> Integer,
        b_id -> Integer,
    }
}

table! {
    b {
        id -> Integer,
        value -> VarChar,
    }
}

table! {
    c {
        id -> Integer,
        can_edit_b -> Bool,
    }
}

joinable!(a -> b (b_id));
joinable!(a -> c (c_id));

allow_tables_to_appear_in_same_query!(a, b, c);

fn example(arg: i32) {
    let all_joined = a::table.inner_join(b::table).inner_join(c::table);
    let matching_rows = all_joined
        .filter(a::c_id.eq(arg))
        .filter(c::can_edit_b.eq(true));

    let update_stmt = diesel::update(b::table)
        .filter(b::id.eq_any(matching_rows.select(b::id)))
        .set(b::value.eq("some value"));

    println!("{}", diesel::debug_query::<diesel::pg::Pg, _>(&update_stmt));
}

fn main() {
    example(42);
}

这将生成与您的SQL不同的SQL，但应导致相同的结果:

UPDATE "b"
SET "value" = $1
WHERE "b"."id" IN
    (SELECT "b"."id"
     FROM (("a"
            INNER JOIN "b" ON "a"."b_id" = "b"."id")
           INNER JOIN "c" ON "a"."c_id" = "c"."id")
     WHERE "a"."c_id" = $2
       AND "c"."can_edit_b" = $3) -- binds: ["some value", 42, true]

也可以看看:

How do I perform a delete with sub-query in Diesel against a Postgres database?