Select after self left join in diesel

help
1

In an attempt to imitate this diesel guide and this stackoverflow post I wrote the following code

use diesel::prelude::*;

mod schema {
    diesel::table! {
        play (id) {
            id -> Int4,
            previous_id -> Nullable<Int4>,
            someuuid -> Uuid,
        }
    }
}

pub fn establish_connection() -> PgConnection {
    let database_url = "postgres:///play";
    PgConnection::establish(&database_url)
        .unwrap_or_else(|_| panic!("Error connecting to {}", database_url))
}

#[derive(Queryable, Clone, Debug)]
pub struct JustAUuid {
    pub data: uuid::Uuid,
}

fn main() {
    let (an_alias, another_alias) =
        diesel::alias!(schema::play as an_alias, schema::play as another_alias);

    let connection = &mut establish_connection();
    let results = an_alias
        .left_join(
            another_alias.on(an_alias
                .field(schema::play::previous_id)
                .eq(another_alias.field(schema::play::id).nullable())),
        )
        .select((
            an_alias.fields((schema::play::someuuid,)),
            another_alias.fields((schema::play::someuuid,)),  // commenting out this line helps
        ))
;       
        // .load::<(JustAUuid, Option<JustAUuid>)>(connection)
        // .expect("something went wrong");
}

I.e. on table entires 1, None, 1234-1234-1234, I want to obtain 1234-1234-1234, None and on those entries where previous_id is set 2, 1, 5555-5555-5555, I want to obtain 5555-5555-5555, Some(1234-1234-1234).

This code does not compile with this error

 2  error[E0277]: Cannot select `AliasedField<another_alias, columns::someuuid>` from `Alias<an_alias>`
   --> src/main.rs:35:10
    |
 35 |         .select((
    |          ^^^^^^ the trait `SelectableExpression<Alias<an_alias>>` is not implemented for `AliasedField<another_alias, columns::someuuid>`
    |
    = note: `AliasedField<another_alias, columns::someuuid>` is no valid selection for `Alias<an_alias>`
    = help: the following other types implement trait `SelectableExpression<QS>`:
              `AliasedField<S, C>` implements `SelectableExpression<Alias<S>>`
              `AliasedField<S, C>` implements `SelectableExpression<JoinOn<Join, On>>`
              `AliasedField<S, C>` implements `SelectableExpression<SelectStatement<FromClause<From>>>`
              `AliasedField<S, C>` implements `SelectableExpression<query_source::joins::Join<Left, Right, Inner>>`
              `AliasedField<S, C>` implements `SelectableExpression<query_source::joins::Join<Left, Right, LeftOuter>>`

With some playing around, I noticed that removing the select part related to another_alias makes the compiler happy. i.e. i think my problem boils down how to write the select such that it selects from the correct of the joined aliases. Comparing to the stackoverflow post, I also noticed that my code compiles when using an inner_join instead of a left_join (replacing the OptionbyJustAUuidin theload`).

Any hints / advice how to select the right part of a self left-join?

2

I would like to point to the documentation of QueryDsl::select

select has slightly stricter bounds on its arguments than other methods. In particular, when used with a left outer join, .nullable must be called on columns that come from the right side of a join. It can be called on the column itself, or on an expression containing that column. title.nullable(), lower(title).nullable(), and (id, title).nullable() would all be valid.

So you should be able to fex that by changing your select call to

        .select((
            an_alias.fields((schema::play::someuuid,)),
            another_alias.fields((schema::play::someuuid,)).nullable(),  // commenting out this line helps
        ))