如何在 Diesel 中针对 Postgres 数据库执行带有子查询的删除? [英] How do I perform a delete with sub-query in Diesel against a Postgres database?

问题描述

我在 Postgres 数据库中有以下架构:

I have the following schema in a Postgres database:

Table A {
  ID
  Name
}

Table B {
  ID FOREIGN KEY (A.ID)
}

我正在尝试在 Diesel 中编写以下查询:

I'm trying to write the following query in Diesel:

DELETE FROM B
WHERE B.ID in (SELECT ID from A WHERE A.Name = $VAR)

$VAR 是我的应用程序传递的变量.

$VAR is a variable passed by my application.

我第一次尝试写的内容如下:

My first attempt to write this was the following:

fn deleteB(conn: &PgConnection, n: &str) {
    use schema::A::dsl::*;
    use schema::A;
    use schema::B;

    let res = A::table
        .filter(Name.eq(n))
        .select(ID);
        .load(conn)
        .unwrap();
    assert!(res.len() < 2);
    let b_id: i32 = *res.iter().nth(1).unwrap_or(&0);

    let _rows = diesel::delete(
        B::table
        .filter(ID.eq(n_id))
    )
    .execute(conn)
    .unwrap();
}

这可以编译但不起作用:ID 的 SELECT 语句总是返回 0.它不匹配 A 中的任何插入记录，即使我手动检查它们在那里.我确定我在完成匹配的方式上有错误(&str 与 &String 也许?)，但我决定尝试不同的解决方案因为无论如何我都不喜欢这个，因为它必须对数据库执行两个单独的语句.

This compiles but it doesn't work: the SELECT statement for the ID always returns 0. It doesn't match any of the inserted records in A even though I checked by hand they are there. I'm sure I have an error in the way the match is being done (&str vs &String maybe?), but I decided to try a different solution since I didn't like this one anyway since it has to execute two separate statements against the database.

我的第二次尝试是这样的:

My second attempt looks like this:

fn deleteB(conn: &PgConnection, n: &str) {
    use schema::A::dsl::*;
    use schema::A;
    use schema::B;

    let source = B::table.inner_join(A::table)
            .filter(Name.eq(n));
    let s = delete(source)
            .execute(conn)
            .unwrap();
}

这对我来说看起来更符合 Diesel 习惯(剧透警告，我几乎不知道框架)但果然，它不会编译:

This looks more Diesel-idiomatic to me (spoiler alert, I barely know the framework) but sure enough, it doesn't compile:

    |
410 |     let s = delete(source)
    |                    ^^^^^^ the trait `diesel::query_builder::IntoUpdateTarget` is not implemented for `diesel::query_builder::SelectStatement<diesel::query_source::joins::JoinOn<diesel::query_source::joins::Join<mobility2::schema::B::table...` (very long error)

当我意识到这似乎是一个微不足道的操作(删除本身)时，我正在研究上述特征，我可能最好寻求帮助.

I was looking into the aforementioned trait when I realised this seems like a trivial operation (the delete itself) and I would probably be better off asking for help.

如何正确编写惯用的 Diesel 代码来执行我要执行的删除语句?

How do I properly write the idiomatic Diesel code to execute the delete statement I want to execute?

推荐答案

由于 Diesel 语句从字面上映射到 SQL，您编写的查询将导致:

As Diesel statements map quite literally to SQL, the query you have written would result in:

DELETE FROM TABLE B INNER JOIN TABLE A ON … WHERE name = $1

那不是有效的 SQL，因此会导致编译时错误.

That's not valid SQL, so it results in a compile time error.

要获得您要编写的查询，您需要执行以下操作:

To get the query you want to write, you need to do something like:

#[macro_use]
extern crate diesel;

use diesel::delete;
use diesel::prelude::*;

mod schema {
    table! {
        A(ID) {
            ID -> Integer,
            name -> Text,
        }
    }

    table! {
        B(ID) {
            ID -> Integer,
        }
    }

    allow_tables_to_appear_in_same_query!(A, B);
}

fn deleteB(conn: &PgConnection, n: &str) {
    use schema::A;
    use schema::B;

    let s = delete(B::table)
        .filter(B::ID.eq_any(A::table.filter(A::name.eq(n)).select(A::ID)))
        .execute(conn)
        .unwrap();
}

这篇关于如何在 Diesel 中针对 Postgres 数据库执行带有子查询的删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！