Delete all rows inside a mapping-table

[feliwir]

Hey, i have 2 SQL tables defined like this:

CREATE TYPE file_state AS ENUM ('available', 'in_use', 'stale');

CREATE TABLE file (
    uid UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(64) NOT NULL,
    byte_size BIGINT NOT NULL,
    path VARCHAR(1024) NOT NULL,
    state file_state NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE import_file (
    import_uid UUID,
    file_uid UUID,
    PRIMARY KEY (import_uid, file_uid),
    FOREIGN KEY (import_uid) REFERENCES import (uid) ON DELETE CASCADE,
    FOREIGN KEY (file_uid) REFERENCES file (uid) ON DELETE RESTRICT
);

I'm struggeling to write an expression that deletes all file rows by import_uid. I tried something like this:

pub async fn delete_all_files(
    conn: &mut DbConn,
    param_uid: &Uuid,
) -> Result<usize, diesel::result::Error> {
    use crate::db::schema::file::dsl::*;
    use crate::db::schema::import_file::dsl::*;

    diesel::delete(file.inner_join(import_file.filter(import_uid.eq(param_uid))))
        .execute(conn)
        .await
}

However that code results in a huge error log: https://gist.github.com/feliwir/7d503c926ad8d9aa78f820d15488163b

From my research it doesn't seem possible to delete on a joined table? How can i possibly achieve what i'm trying to do here?
Thanks a lot!

[weiznich]

The compilation error is expected behavior as it would generate SQL like DELETE FROM files inner join import_file WHERE import_uid = $1 which is just not valid SQL. You likely want to use a subquery instead of the join. So something like this should work:

diesel::delete(file.filter(uid.eq_any(import_file.filter(import_id.eq(param_uid)).select(import_file::file_uid))).execute(conn)

should work fine