如何使用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的实例被同样引用b和c.can_edit_b的a的实例引用即可。我想做的事情的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

我在Diesel的API中找不到与SQLfrom相对应的相关方法/函数。如果我尝试使用inner_join，那么编译器告诉我没有为UpdateStatement定义inner_join。

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，但应该会产生相同的结果：

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]

另请参阅：

• 如何在Diesel中对Postgres数据库执行带子查询的删除