[diesel] Deduce boxed query type with outer join?

I have schema and table:

diesel::table! {
    farm_device (id) {
        id -> Int8,
        added_at -> Nullable<Timestamptz>,
        modified_at -> Nullable<Timestamptz>,
        #[max_length = 20]
        name -> Nullable<Varchar>,
        #[max_length = 10]
        serial_number -> Nullable<Varchar>,
        #[max_length = 8]
        device_type -> Varchar,
        mac -> Nullable<Int8>,
        is_active -> Bool,
        farm_id -> Nullable<Int8>,
        added_by_id -> Nullable<Uuid>,
        modified_by_id -> Nullable<Uuid>,
    }
}

diesel::table! {
    farm_actuator (id) {
        id -> Int8,
        added_at -> Nullable<Timestamptz>,
        modified_at -> Nullable<Timestamptz>,
        #[max_length = 9]
        condition_type -> Nullable<Varchar>,
        #[max_length = 200]
        name -> Varchar,
        affecting_direction -> Int2,
        operation_range -> Nullable<Numrange>,
        #[max_length = 200]
        reason -> Nullable<Varchar>,
        mqtt_number -> Int2,
        pwm_level_max -> Nullable<Int2>,
        room_id -> Int8,
        device_id -> Nullable<Int8>,
        added_by_id -> Nullable<Uuid>,
        modified_by_id -> Nullable<Uuid>,
        dependent_pump_id -> Nullable<Int8>,
    }
}

#[derive(
    Debug, Serialize, Deserialize, Clone, Queryable, Insertable, AsChangeset, Identifiable, Associations, Selectable,
)]
#[diesel(table_name=farm_actuator, primary_key(id), belongs_to(Device, foreign_key=device_id) , belongs_to(Room, foreign_key=room_id))]
pub struct Actuator {   
    pub id: i64,
    pub added_at: Option<DateTime<Utc>>,
    pub modified_at: Option<DateTime<Utc>>,
    pub condition_type: Option<ControlledConditionType>,
    pub name: String,
    pub affecting_direction: AffectingDirection,
    pub operation_range: Option<(Bound<BigDecimal>, Bound<BigDecimal>)>,
    pub reason: Option<String>, 
    pub mqtt_number: i16,
    pub pwm_level_max: Option<i16>,
    pub room_id: i64,
    pub device_id: Option<i64>,
    pub added_by_id: Option<Uuid>,
    pub modified_by_id: Option<Uuid>,
    pub dependent_pump_id: Option<i64>,
}

where dependent_pump_id points to the same table.

I want to make a box query to select Actuator which doesn't have dependent_pump_id pointing to it.

// Get actuators which are not pumps bound with solenoid valves
pub fn direct_schedulable_actuators(&self, conn: &mut PgConnection) -> QueryResult<Vec<Actuator>> {
use crate::schema::farm_actuator::dsl::*;
diesel::alias!(crate::schema::farm_actuator as actuators_alias: ActuatorAlias);

farm_actuator
    .left_outer_join(actuators_alias.on(id.nullable().eq(actuators_alias.field(dependent_pump_id))))
    .filter(actuators_alias.field(id).is_null())
    .select(Actuator::as_select())
    .inner_join(farm_device::table)
    .filter(device_id.is_not_null())
    .filter(farm_device::is_active.eq(true))
    .filter(room_id.eq(self.id))
    .get_results(conn) 
}

but still fail to do. This is my attempt:

diesel::alias!(crate::schema::farm_actuator as actuators_alias: ActuatorAlias);

// Atttempt to define type for box query (not successfully yet)
pub type ActuatorSelfLeftJoin = LeftJoinOn<
    farm_actuator::table,
    ActuatorAlias,
    dsl::Eq<dsl::Nullable<farm_actuator::id>, AliasedField<ActuatorAlias, farm_actuator::dependent_pump_id>>,
>;

pub type LeftJoinNonPumpActuator =
    dsl::Filter<ActuatorSelfLeftJoin, dsl::IsNull<AliasedField<ActuatorAlias, farm_actuator::id>>>;

pub type DirectSchedulableActuatorQuery = dsl::IntoBoxed<
    'static,
    dsl::Filter<
        dsl::Filter<
            dsl::Filter<
                dsl::InnerJoin<dsl::Select<LeftJoinNonPumpActuator, dsl::AsSelect<Actuator, Pg>>, farm_device::table>,
                dsl::IsNotNull<farm_actuator::device_id>,
            >,
            dsl::Eq<farm_device::is_active, bool>,
        >,
        dsl::Eq<farm_actuator::room_id, i64>,
    >,
    Pg,
>;

Could someone point out what is wrong / missing in the type definition above?

Its hard to help you with that as it's not clear where exactly you want to use the constructed type exactly. Your code example does not show that. Additionally it would be very helpful to also have the complete error message that is emitted by rustc.

Sorry, it is used here:

pub fn query_for_direct_schedulable_actuators(&self, conn: &mut PgConnection) -> DirectSchedulableActuatorQuery {
    use crate::schema::farm_actuator::dsl::*;

    farm_actuator
        .left_outer_join(actuators_alias.on(id.nullable().eq(actuators_alias.field(dependent_pump_id))))
        .filter(actuators_alias.field(id).is_null())
        .select(Actuator::as_select())
        .inner_join(farm_device::table)
        .filter(device_id.is_not_null())
        .filter(farm_device::is_active.eq(true))
        .filter(room_id.eq(self.id))
        .into_boxed()
}

And the compiler error is:

error[E0277]: the trait bound `SelectStatement<FromClause<schema::farm_actuator::table>>: InternalJoinDsl<farm::ActuatorAlias, LeftOuter, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<NullableExpression<schema::farm_actuator::columns::id>, AliasedField<farm::ActuatorAlias, schema::farm_actuator::columns::dependent_pump_id>>>>` is not satisfied
   --> src/models/farm.rs:402:86
    |
402 |     pub fn query_for_direct_schedulable_actuators(&self, conn: &mut PgConnection) -> DirectSchedulableActuatorQuery {
    |                                                                                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ the trait `InternalJoinDsl<farm::ActuatorAlias, LeftOuter, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<NullableExpression<schema::farm_actuator::columns::id>, AliasedField<farm::ActuatorAlias, schema::farm_actuator::columns::dependent_pump_id>>>>` is not implemented for `SelectStatement<FromClause<schema::farm_actuator::table>>`, which is required by `schema::farm_actuator::table: InternalJoinDsl<farm::ActuatorAlias, LeftOuter, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<NullableExpression<schema::farm_actuator::columns::id>, AliasedField<farm::ActuatorAlias, schema::farm_actuator::columns::dependent_pump_id>>>>`
    |
    = help: the following other types implement trait `InternalJoinDsl<Rhs, Kind, On>`:
              SelectStatement<FromClause<F>, query_builder::select_clause::DefaultSelectClause<FromClause<F>>, D, W, O, LOf, G, H, LC>
              SelectStatement<FromClause<F>, query_builder::select_clause::SelectClause<S>, D, W, O, LOf, G, H, LC>
    = note: required for `schema::farm_actuator::table` to implement `InternalJoinDsl<farm::ActuatorAlias, LeftOuter, diesel::expression::grouped::Grouped<diesel::expression::operators::Eq<NullableExpression<schema::farm_actuator::columns::id>, AliasedField<farm::ActuatorAlias, schema::farm_actuator::columns::dependent_pump_id>>>>`

Hura, I finally solve it, by replacing:

pub type ActuatorSelfLeftJoin = LeftJoinOn<
    farm_actuator::table,
    ActuatorAlias,
    dsl::Eq<dsl::Nullable<farm_actuator::id>, AliasedField<ActuatorAlias, farm_actuator::dependent_pump_id>>,
>;

with

pub type ActuatorSelfLeftJoin = LeftJoinOn<
    farm_actuator::table,
    Alias<ActuatorAlias>,
    dsl::Eq<dsl::Nullable<farm_actuator::id>, AliasedField<ActuatorAlias, farm_actuator::dependent_pump_id>>,
>;
1 Like