How do I use a join table?

[felixwatts]

Hi sorry but I cant find any documentation or examples on how to achieve this:

My schema is:

table! {
    bed_notes (id) {
        id -> Int4,
        bed_id -> Nullable<Int4>,
        note_id -> Nullable<Int4>,
    }
}

table! {
    beds (id) {
        id -> Int4,
        name -> Varchar,
    }
}

table! {
    notes (id) {
        id -> Int4,
        time -> Timestamp,
        text -> Text,
    }
}

joinable!(bed_notes -> beds (bed_id));
joinable!(bed_notes -> notes (note_id));

allow_tables_to_appear_in_same_query!(
    bed_notes,
    beds,
    notes,
);

Now, given a bed id I want to select all the associated notes.

I've tried variations around:

    let notes = bed_notes::table
        .left_join(beds::table)
        .left_join(notes::table)
        .filter(bed_notes::bed_id.eq(id))
        .select((notes::dsl::id, notes::dsl::time, notes::dsl::text))
        .load::<Note>(&connection)
        .expect("Error loading bed notes");

But I get compile error:

error[E0271]: type mismatch resolving `<schema::notes::table as AppearsInFromClause<schema::notes::table>>::Count == Never`
  --> src/main.rs:84:10
   |
84 |         .load::<Note>(&connection)
   |          ^^^^ expected struct `diesel::query_source::Once`, found struct `Never`

Would it be possible to document how to achieve this or point me towards those docs if they already exist?

Many thanks!

[weiznich]

A left join makes all columns coming from the joined table Nullable. Diesel expresses this by requiring you to call .nullable() on the corresponding fields in your select clause. See this documentation for examples.

As another semi-related note: You don't need to join beds::table in this case, as this table is not used anywhere in your query.

A fixed version of your example would look like:

    let notes = bed_notes::table
        .left_join(notes::table)
        .filter(bed_notes::bed_id.eq(id))
        .select((notes::dsl::id.nullable(), notes::dsl::time.nullable(), notes::dsl::text.nullable()))
        .load::<Note>(&connection)
        .expect("Error loading bed notes");

From looking at our documentation it seems like we should also mention this in the documentation of left_join. Mind opening a PR that adds the example form the select method there?

[felixwatts]

Ah thanks for that. PR as requested: #2773