Rust Diesel left join on multiple conditions

Question

I am having difficulty determining from Diesel's documentation how to express this query with Diesel (without using raw_sql):

SELECT *
FROM budgets AS b 
LEFT JOIN user_budgets AS ub
ON (ub.budget_id = {budget_id} 
    AND ub.user_id = {user_id})
WHERE b.id = {budget_id}
LIMIT 1;

{budget_id} and {user_id} are variable. This is what I've tried:

let budget = budgets
    .select(budget_fields::all_columns)
    .left_join(user_budgets.on(user_budget_fields::budget_id.eq(budget_id)))
    .left_join(user_budget_fields::user_id.eq(user_id))
    .filter(budget_fields::id.eq(budget_id))
    .first::<Budget>(db_connection)?;

but I get this error:

error[E0277]: the trait bound `budgets::table: JoinTo<diesel::expression::operators::Eq<user_budgets::columns::user_id, diesel::expression::bound::Bound<diesel::sql_types::Uuid, uuid::Uuid>>>` is not satisfied
  --> src/utils/db/budget.rs:46:10
   |
46 |         .left_join(user_budget_fields::user_id.eq(user_id))
   |          ^^^^^^^^^ the trait `JoinTo<diesel::expression::operators::Eq<user_budgets::columns::user_id, diesel::expression::bound::Bound<diesel::sql_types::Uuid, uuid::Uuid>>>` is not implemented for `budgets::table`
   |
   = help: the following other types implement trait `JoinTo<T>`:
             <budgets::table as JoinTo<JoinOn<Join, On>>>
             <budgets::table as JoinTo<diesel::query_builder::BoxedSelectStatement<'a, QS, ST, DB>>>
             <budgets::table as JoinTo<diesel::query_builder::SelectStatement<F, S, D, W, O, L, Of, G>>>
             <budgets::table as JoinTo<diesel::query_source::joins::Join<Left, Right, Kind>>>
   = note: required because of the requirements on the impl of `JoinTo<diesel::expression::operators::Eq<user_budgets::columns::user_id, diesel::expression::bound::Bound<diesel::sql_types::Uuid, uuid::Uuid>>>` for `diesel::query_source::joins::Join<budgets::table, user_budgets::table, LeftOuter>`
   = note: required because of the requirements on the impl of `JoinWithImplicitOnClause<diesel::expression::operators::Eq<user_budgets::columns::user_id, diesel::expression::bound::Bound<diesel::sql_types::Uuid, uuid::Uuid>>, LeftOuter>` for `diesel::query_builder::SelectStatement<JoinOn<diesel::query_source::joins::Join<budgets::table, user_budgets::table, LeftOuter>, diesel::expression::operators::Eq<user_budgets::columns::budget_id, diesel::expression::bound::Bound<diesel::sql_types::Uuid, uuid::Uuid>>>, query_builder::select_clause::SelectClause<(budgets::columns::id, budgets::columns::is_shared, budgets::columns::is_private, budgets::columns::is_deleted, budgets::columns::name, budgets::columns::description, budgets::columns::start_date, budgets::columns::end_date, budgets::columns::latest_entry_time, budgets::columns::modified_timestamp, budgets::columns::created_timestamp)>>`

For more information about this error, try `rustc --explain E0277`.

I have also tried this:

let budget = budgets
    .select(budget_fields::all_columns)
    .left_join(
        user_budgets
            .on(user_budget_fields::budget_id.eq(budget_id))
            .and(user_budget_fields::user_id.eq(user_id)),
    )
    .filter(budget_fields::id.eq(budget_id))
    .first::<Budget>(db_connection)?;

but I get this error:

error[E0599]: the method `and` exists for struct `OnClauseWrapper<user_budgets::table, diesel::expression::operators::Eq<user_budgets::columns::budget_id, diesel::expression::bound::Bound<diesel::sql_types::Uuid, uuid::Uuid>>>`, but its trait bounds were not satisfied
   --> src/utils/db/budget.rs:48:18
    |
48  |                 .and(user_budget_fields::user_id.eq(user_id)),
    |                  ^^^ method cannot be called on `OnClauseWrapper<user_budgets::table, diesel::expression::operators::Eq<user_budgets::columns::budget_id, diesel::expression::bound::Bound<diesel::sql_types::Uuid, uuid::Uuid>>>` due to unsatisfied trait bounds
    |
   ::: /Users/tanner/.cargo/registry/src/github.com-1ecc6299db9ec823/diesel-1.4.8/src/query_source/joins.rs:281:1
    |
281 | pub struct OnClauseWrapper<Source, On> {
    | --------------------------------------
    | |
    | doesn't satisfy `<_ as diesel::Expression>::SqlType = diesel::sql_types::Bool`
    | doesn't satisfy `_: BoolExpressionMethods`
    | doesn't satisfy `_: diesel::Expression`
    |
    = note: the following trait bounds were not satisfied:
            `<OnClauseWrapper<user_budgets::table, diesel::expression::operators::Eq<user_budgets::columns::budget_id, diesel::expression::bound::Bound<diesel::sql_types::Uuid, uuid::Uuid>>> as diesel::Expression>::SqlType = diesel::sql_types::Bool`
            which is required by `OnClauseWrapper<user_budgets::table, diesel::expression::operators::Eq<user_budgets::columns::budget_id, diesel::expression::bound::Bound<diesel::sql_types::Uuid, uuid::Uuid>>>: BoolExpressionMethods`

For more information about this error, try `rustc --explain E0599`.

What am I missing here?

Answer 1

You definitely want a single left_join. In your second attempt, the error is that the and needs to be inside the call to on, not after it (the argument to on must be the full boolean expression).

let budget = budgets
    .select(budget_fields::all_columns)
    .left_join(
        user_budgets.on(
            user_budget_fields::budget_id.eq(budget_id)
            .and(user_budget_fields::user_id.eq(user_id))),
    )
    .filter(budget_fields::id.eq(budget_id))
    .first::<Budget>(db_connection)?;