Operations (e.g. multiply) on columns from a join in select_expression

[AdamKuziemski]

I've only found #3970 regarding multiplication, but this seems to be a different case.

For reference, I'm running Diesel 2.2.6.
I'm currently working on an app that handles medication for animals, and I have these two (abbreviated) tables:

diesel::table! {
    species_substance_safety (substance_id) {
        maximum_milligrams_per_kilo_per_day -> Float8,
        milligrams_per_kilo_per_day -> Float8,
        substance_id -> Uuid,
    }
}

diesel::table! {
    medicine_ingredients (substance_id) {
        dosage_per_unit -> Float8,
        substance_id -> Uuid,
    }
}

I want to run a join with this (also abbreviated) struct as a result (the select_expressions are something I'd eventually like to do):

use crate::schema::{medicine_ingredients, species_substance_safety};

#[derive(Queryable, Selectable)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct SubstanceSafetyForSpecies {
    #[diesel(select_expression = 
        species_substance_safety::maximum_milligrams_per_kilo_per_day *
        medicine_ingredients::dosage_per_unit
    )]
    pub maximum_dose_per_kilo_per_day: f64,
    #[diesel(select_expression =
         species_substance_safety::milligrams_per_kilo_per_day *
         medicine_ingredients::dosage_per_unit
    )]
    pub recommended_dose_per_kilo_per_day: f64,
}

I'm running this join:

species_substance_safety::table
    .inner_join(substances::table.inner_join(medicine_ingredients::table))
    .filter(species_substance_safety::substance_id.eq_any(substance_ids))
    .select(Self::as_select())
    .load::<Self>(conn)

I have a hunch that a function with auto_type could work here, but so far I haven't been able to use it to construct a valid query for this.

I can select the results into a tuple, but for the life of me I can't get the select_expression to work. Is that even possible?

[weiznich]

Can you provide the error message that you get? I cannot even reproduce your problem as the following code compiles without error for me:

mod schema {
    diesel::table! {
        species_substance_safety (substance_id) {
            maximum_milligrams_per_kilo_per_day -> Float8,
            milligrams_per_kilo_per_day -> Float8,
            substance_id -> Uuid,
        }
    }

    diesel::table! {
        medicine_ingredients (substance_id) {
            dosage_per_unit -> Float8,
            substance_id -> Uuid,
        }
    }

    diesel::table! {
        substances {
            id -> Uuid,
        }
    }

    diesel::allow_tables_to_appear_in_same_query!(
        species_substance_safety,
        medicine_ingredients,
        substances
    );

    diesel::joinable!(medicine_ingredients -> substances (substance_id));
    diesel::joinable!(species_substance_safety -> substances (substance_id));
}

use crate::schema::{medicine_ingredients, species_substance_safety, substances};

#[derive(Queryable, Selectable)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct SubstanceSafetyForSpecies {
    #[diesel(select_expression =
        species_substance_safety::maximum_milligrams_per_kilo_per_day *
        medicine_ingredients::dosage_per_unit
    )]
    pub maximum_dose_per_kilo_per_day: f64,
    #[diesel(select_expression =
         species_substance_safety::milligrams_per_kilo_per_day *
         medicine_ingredients::dosage_per_unit
    )]
    pub recommended_dose_per_kilo_per_day: f64,
}

fn main() {
    let conn = &mut PgConnection::establish("_").unwrap();

    let substance_ids = Vec::<uuid::Uuid>::new();
    species_substance_safety::table
        .inner_join(substances::table.inner_join(medicine_ingredients::table))
        .filter(species_substance_safety::substance_id.eq_any(substance_ids))
        .select(SubstanceSafetyForSpecies::as_select())
        .load::<SubstanceSafetyForSpecies>(conn);
}

[AdamKuziemski]

Thanks for replying.

I get either:

unsupported expression for auto_type, please provide a type hint
rust-analyzer[macro-error](https://rust-analyzer.github.io/book/diagnostics.html#macro-error)

error[E0121]: the placeholder `_` is not allowed within types on item signatures for associated types
  --> src/medicines/models/substance_safety_entity.rs:20:9
   |
20 |         species_substance_safety::maximum_milligrams_per_kilo_per_day *
   |         ^^^^^^^^^^^^^^^^^^^^^^^^ not allowed in type signatures
...
25 |          species_substance_safety::recommended_milligrams_per_kilo_per_day *
   |          ^^^^^^^^^^^^^^^^^^^^^^^^ not allowed in type signatures

or, if I add select_expression_type = Float8:

   error[E0308]: mismatched types
  --> src/medicines/models/substance_safety_entity.rs:43:29
   |
43 |         select_expression = species_substance_safety::maximum_milligrams_per_kilo_per_day * medicine_ingredients::dosage_per_unit,
   |                             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ expected `Double`, found `Mul<maximum_milligrams_per_kilo_per_day, ...>`
   |
   = note: expected struct `Double`
              found struct `diesel::expression::ops::numeric::Mul<species_substance_safety::columns::maximum_milligrams_per_kilo_per_day, medicine_ingredients::columns::dosage_per_unit>`

Edit: just to add, I get the exact same errors if I extract the multiplication into a function:

// `_` gets the first error, if I change the return type to `Float8`, I'll get the second error
#[diesel::dsl::auto_type]
fn calculate_max_dose() -> _ {
    dosage_per_unit * maximum_milligrams_per_kilo_per_day
}

[weiznich]

Can you clarify which version of diesel_derives is in your dependency tree? We did some recent improvements there, so if I use a newer version and you an older version that would explain why it works for me and it doesn't work for you. (It would also explain how you can fix that 😉)

[AdamKuziemski]

In my cargo.toml, there's this:
diesel = { version = "2.2.6", features = ["postgres", "chrono", "uuid", "network-address"] }
In the lock file:

[[package]]
name = "diesel_derives"
version = "2.2.3"
source = "registry+https://github.com/rust-lang/crates.io-index"
checksum = "e7f2c3de51e2ba6bf2a648285696137aaf0f5f487bcbea93972fe8a364e131a4"
dependencies = [
 "diesel_table_macro_syntax",
 "dsl_auto_type",
 "proc-macro2",
 "quote",
 "syn 2.0.92",
]

[weiznich]

Thanks for providing these information. I tracked it down now. It's not the diesel_derives version, but the dsl_auto_type version that's relevant. I can reproduce your error with version 0.1.2 or older. Version 0.1.3 fixes this by introducing support for this kind of expressions, so the solution to your problem is to execute cargo update -p dsl_auto_type to update that package. After that it will just work.

[AdamKuziemski]

That was it, thank you so much!
Thanks for your work on the library, too 🫡