CODEWITHSUNDEEP
sqlrustrust-diesel
Teymour
Teymour

Reputation: 2079

How do I perform an `UPDATE FROM` using Diesel?

Suppose I have three tables, a, b and 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)
);

I want to update b (given an id for an instance of c) so long as that instance of c is referenced by an instance of a which also references b and c.can_edit_b is true. The SQL for what I want to do:

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

I can't find the relevant method/function in Diesel's API which corresponds to an SQL from. If I try and use inner_join then the compiler tells me that inner_join is not defined for UpdateStatement.

Upvotes: 0

Views: 1599

Answers (1)

Shepmaster
Shepmaster

Reputation: 431739

You can join the tables, apply a filter, then use that as the update condition:

#[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);
}

This generates SQL that differs from yours, but should result in the same outcome:

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]

See also:

Upvotes: 3

Related Questions