Unable to `inner_join` to multiple tables (A->B and A->C, not A->B->C)

[trombonehero]

Setup

Versions

• Rust: 1.23.0-nightly (8b22e70b2 2017-10-31)
• Diesel: 0.16.0
• Database: postgres
• Operating System: any

Feature Flags

• diesel:
• diesel_codegen: [ "postgres" ]

Problem Description

What are you trying to accomplish?

I have a reduced test case that involves three types of DB-backed objects: Room, User and Booking. A Booking object represents a User having reserved a Room (e.g., for a meeting):

create table bookings (
	id serial primary key not null,
	room_id integer not null,
	user_id integer not null,

	foreign key (room_id) references rooms(id),
	foreign key (user_id) references users(id)
);

When I run diesel print-schema I see:

[...]
joinable!(bookings -> rooms (room_id));
joinable!(bookings -> users (user_id));

I'm also able to use a single inner_join between Booking and either Room or User:

/* ... */

#[derive(Debug, Identifiable, Queryable)]
pub struct Booking {
    pub id: i32,
    pub room_id: i32,
    pub user_id: i32,
}

type DieselResult<T> = Result<T, diesel::result::Error>;

impl Booking {
    pub fn with_rooms(c: &diesel::PgConnection) -> DieselResult<Vec<(Booking, Room)>> {
        use self::bookings::dsl::*;
        bookings.inner_join(rooms::table).load(c)
    }

    pub fn with_users(c: &diesel::PgConnection) -> DieselResult<Vec<(Booking, User)>> {
        use self::bookings::dsl::*;
        bookings.inner_join(users::table).load(c)
    }
}

Next, I want to be able to retrieve a Vec<(Booking, Room, User)> from the database by joining both bookings->rooms and bookings->users in the same query.

What is the expected output?

When I try to compile:

impl Booking {
    /* ... */
    pub fn full(c: &diesel::PgConnection) -> DieselResult<Vec<(Booking, Room, User)>> {
        use self::bookings::dsl::*;

        bookings.inner_join(rooms::table)
                .inner_join(users::table)
                .load(c)
    }
}

I would expect Diesel to generate a query like:

SELECT * from bookings
    INNER JOIN rooms ON rooms.id = room_id
    INNER JOIN users ON users.id = user_id

(i.e., joining bookings->rooms and bookings->users)

What is the actual output?

error[E0277]: the trait bound `__diesel_infer_schema::infer_rooms::rooms::table:  diesel::JoinTo<__diesel_infer_schema::infer_users::users::table>` is not satisfied
  --> src/lib.rs:47:18
   |
47 |                 .inner_join(users::table)
   |                  ^^^^^^^^^^ the trait `diesel::JoinTo<__diesel_infer_schema::infer_users::users::table>` is not implemented for `__diesel_infer_schema::infer_rooms::rooms::table`

So, Diesel seems to be trying to join bookings->rooms and rooms->users. I would've expected this if I were trying to compile:

bookings.inner_join(
    rooms::table.inner_join(
        users::table
    )
)

but in my use case, I'm trying to join both rooms and users with bookings individually.

Steps to reproduce

1. Create an empty database, set DATABASE_URL
2. Clone https://github.com/trombonehero/diesel-join-example
3. Execute cargo build

Checklist

• I have already looked over the issue tracker for similar issues.

[weiznich]

Try to add enable_multi_table_joins!(rooms, users); somewhere in your crate.

[trombonehero]

That does seem to fix it... thank you!

Now I suppose the question is: what documentation fixes might help others avoid this particular pothole in the future?

[sgrif]

what documentation fixes might help others avoid this particular pothole in the future?

We've been trying to document it a bit more in the relevant methods for the upcoming release. It appears in the docs for JoinDsl (where the methods you are calling come from), as well as joinable! (which had to be invoked for this to work at all).

Additionally on master, both infer_schema! and diesel print-schema will generate code which invokes this for every table in your schema, making this a non-issue for most people

[trombonehero]

Ok, so it sounds like v0.17.0 will “just work”? If so, that’s great! Thanks.

[sgrif]

0.99 but yes