Compilation fails when attempting to create a nested join from an aliased table.

[JSFTI]

Setup

Versions

• Rust: 1.72.0
• Diesel: 2.1.1
• Database: SQLite
• Operating System Ubuntu 20.04.6 LTS

Feature Flags

• diesel: "sqlite"

Problem Description

Compilation fails when attempting to create a nested join from an aliased table. See this discussion.

Error messages:

error[E0277]: the trait bound `SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>: Table` is not satisfied
  --> src/main.rs:46:10
   |
46 |         .inner_join(schema::users::table.inner_join(schema::companies::table))
   |          ^^^^^^^^^^ the trait `Table` is not implemented for `SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>`
   |
   = help: the following other types implement trait `Table`:
             categories::table
             companies::table
             users::table
   = note: required for `Alias<category_child>` to implement `JoinTo<SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>>`
   = note: 1 redundant requirement hidden
   = note: required for `query_source::joins::Join<Alias<category_child>, Alias<category_parent>, Inner>` to implement `JoinTo<SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>>`
   = note: required for `SelectStatement<FromClause<JoinOn<Join<Alias<category_child>, Alias<category_parent>, Inner>, Grouped<...>>>>` to implement `JoinWithImplicitOnClause<SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>, Inner>`

error[E0277]: the trait bound `SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>: Table` is not satisfied
  --> src/main.rs:46:10
   |
46 |         .inner_join(schema::users::table.inner_join(schema::companies::table))
   |          ^^^^^^^^^^ the trait `Table` is not implemented for `SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>`
   |
   = help: the following other types implement trait `Table`:
             categories::table
             companies::table
             users::table
   = note: required for `Alias<category_child>` to implement `JoinTo<SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>>`
   = note: 1 redundant requirement hidden
   = note: required for `query_source::joins::Join<Alias<category_child>, Alias<category_parent>, Inner>` to implement `JoinTo<SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>>`
   = note: required for `SelectStatement<FromClause<JoinOn<Join<Alias<category_child>, Alias<category_parent>, Inner>, Grouped<...>>>>` to implement `JoinWithImplicitOnClause<SelectStatement<FromClause<JoinOn<query_source::joins::Join<users::table, companies::table, Inner>, expression::grouped::Grouped<expression::operators::Eq<NullableExpression<users::columns::company_id>, NullableExpression<companies::columns::id>>>>>>, Inner>`

What are you trying to accomplish?

Create a nested join statement from an aliased table.

What is the expected output?

Code compiles.

What is the actual output?

Compilation fails

Are you seeing any additional errors?

No

Steps to reproduce

Create the schema definition file.

diesel::table! {
    categories (id) {
        id -> Integer,
        user_id -> Integer,
        name -> Text,
        parent_id -> Nullable<Integer>,
    }
}

diesel::table! {
    companies (id) {
        id -> Integer,
        name -> Text,
    }
}

diesel::table! {
    users (id) {
        id -> Integer,
        company_id -> Integer,
        name -> Text,
    }
}

diesel::joinable!(categories -> users (user_id));
diesel::joinable!(users -> companies (company_id));

diesel::allow_tables_to_appear_in_same_query!(
    categories,
    companies,
    users,
);

The main.rs

pub mod schema;

use diesel::prelude::*;
use diesel::JoinOnDsl;

#[derive(Identifiable, Queryable, Selectable, Associations, Debug)]
#[diesel(belongs_to(Category, foreign_key = parent_id))]
#[diesel(belongs_to(User, foreign_key = user_id))]
#[diesel(table_name = schema::categories)]
#[allow(unused)]
struct Category {
    id: i32,
    user_id: i32,
    name: String,
    parent_id: Option<i32>
}

#[derive(Identifiable, Queryable, Selectable, Associations, Debug)]
#[diesel(belongs_to(Company, foreign_key = company_id))]
#[diesel(table_name = schema::users)]
#[allow(unused)]
struct User {
    id: i32,
    company_id: i32,
    name: String
}

#[derive(Identifiable, Queryable, Selectable, Debug)]
#[diesel(table_name = schema::companies)]
#[allow(unused)]
struct Company {
    id: i32,
    name: String
}

fn main(){
    let mut conn = SqliteConnection::establish("sqlite://src/db.sqlite").unwrap();

    let (category_child, category_parent) = diesel::alias!(
        schema::categories as category_child,
        schema::categories as category_parent,
    );

    let data : (Category, Option<Category>, (User, Company)) = category_child
        .left_join(category_parent.on(
            category_child.field(schema::categories::parent_id).eq(category_parent.field(schema::categories::id).nullable())
        ))
        // Error
        .inner_join(schema::users::table.inner_join(schema::companies::table))
        .select((
            category_child.fields((
                schema::categories::id,
                schema::categories::user_id,
                schema::categories::name,
                schema::categories::parent_id,
            )),
            category_parent.fields((
                schema::categories::id,
                schema::categories::user_id,
                schema::categories::name,
                schema::categories::parent_id,
            )).nullable(),
            (User::as_select(), Company::as_select())
        ))
        .first(&mut conn).unwrap();

    println!("{:#?}", data);
}

I created a repository to reproduce this error.

Checklist

• I have already looked over the issue tracker and the discussion forum for similar possible closed issues.

• This issue can be reproduced on Rust's stable channel. (Your issue will be
  closed if this is not the case)
• This issue can be reproduced without requiring a third party crate