Diesel left join with a select statement

help
1

I have the following tables

CREATE TABLE musics (
  id SERIAL PRIMARY KEY
);
CREATE TABLE tags  (
  id SERIAL PRIMARY KEY,
  music_id INTEGER REFERENCES musics(id) NOT NULL
)

I'd like to run the following query with Diesel.

SELECT musics.id
FROM (
  musics LEFT OUTER JOIN
  (SELECT tags.music_id FROM tags WHERE tags.id = 2) AS selected_tags
  ON (musics.id = selected_tags.music_id))
WHERE (selected_tags.music_id IS NULL)

The following does not work:

pub fn music_without_tag(c: &PgConnection, tag_id: i32) -> Vec<i32> {
    let filtered_tags = tags::table
        .select(tags::music_id)
        .filter(tags::id.eq(tag_id));
    musics::table
        .select(musics::id)
        .left_outer_join(filtered_tags.on(musics::id.eq(tags::music_id)))
        .filter(tags::music_id.is_null())
        .load(c)
        .unwrap()
}

Because the filtered_tags diesel::query_builder::SelectStatement does not implement the diesel::JoinOnDsl.

The complete code is here.

Is there a way to express the desired query?

2

I'm not quite certain what you are trying to do.

Edit: finally figured out the join: it seems you are looking for music that isn't tagged with tag 2?

Might a where not exist query be a (slow) workaround?

3

it seems you are looking for music that isn’t tagged with tag 2?

Yes this is what I am trying to do.

Might a where not exist query be a (slow) workaround?

Yes that should work. But in the end I changed the data structure and stored the tags on the musics table in a Postgres array. With proper indexing (GIN index) it is fast enough.

Thanks @juleskers for looking into it.

1 Like