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?