Multiply value by literal/constant

[marcoschulte]

Hi everyone,

unfortunately I cannot figure out how I would multiply a value by a constant, in my case -1.

To make it more clear, here is some very basic example:

transactions::table
    .select(
        sum(transactions::value).mul(/* how to do that? */ -1)
    )
.load::<(Option<BigDecimal>)>(conn)

Is there a way to write that? I tried something along the lines of -1.into_sql::<Numeric>(), but whatever I tried I had no success.

Would be glad for any help!

[weiznich]

That's just

transactions::table
    .select(
        sum(transactions::value) * BigDecimal::from_i8(-1).unwrap()
    )
.load::<(Option<BigDecimal>)>(conn)

You can use the usual rust operators (+, -, *, /) there to construct the corresponding query. That works as long as the rust side value is compatible to the SQL side type. In that case I assume that your sum() call returns a Numeric value, so it's required to use a BigDecimal there.

[marcoschulte]

Thanks for the super quick response! And it's amazing that this is possible, although I have no idea how I would have figured that out by reading the docs 😅 But that's a different story.

Anyways, unfortunately it does not work, and I seem to be unable to figure out why. Compiling your suggestion gives me the following error

error[E0277]: the trait bound `BigDecimal: ToSql<diesel::sql_types::Numeric, Sqlite>` is not satisfied
    --> src/repository/transaction_repository.rs:190:43
     |
190  |             .load::<(Option<BigDecimal>)>(conn).unwrap();
     |              ----                         ^^^^ the trait `ToSql<diesel::sql_types::Numeric, Sqlite>` is not implemented for `BigDecimal`, which is required by `SelectStatement<FromClause<schema::transactions::table>, diesel::query_builder::select_clause::SelectClause<expression::ops::numeric::Mul<functions::aggregate_folding::sum::sum<diesel::sql_types::BigInt, schema::transactions::columns::value>, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Numeric>, BigDecimal>>>>: LoadQuery<'_, _, std::option::Option<BigDecimal>>`
     |              |
     |              required by a bound introduced by this call
     |
     = help: the trait `ToSql<diesel::sql_types::Nullable<diesel::sql_types::Numeric>, __DB>` is implemented for `BigDecimal`
     = note: required for `BigDecimal` to implement `ToSql<diesel::sql_types::Nullable<diesel::sql_types::Numeric>, Sqlite>`
     = note: required for `expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Numeric>, BigDecimal>` to implement `QueryFragment<Sqlite>`
     = note: 6 redundant requirements hidden
     = note: required for `SelectStatement<FromClause<schema::transactions::table>, diesel::query_builder::select_clause::SelectClause<expression::ops::numeric::Mul<functions::aggregate_folding::sum::sum<diesel::sql_types::BigInt, schema::transactions::columns::value>, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Numeric>, BigDecimal>>>>` to implement `QueryFragment<Sqlite>`
     = note: required for `SelectStatement<FromClause<schema::transactions::table>, diesel::query_builder::select_clause::SelectClause<expression::ops::numeric::Mul<functions::aggregate_folding::sum::sum<diesel::sql_types::BigInt, schema::transactions::columns::value>, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Numeric>, BigDecimal>>>>` to implement `LoadQuery<'_, _, std::option::Option<BigDecimal>>`
note: required by a bound in `diesel::RunQueryDsl::load`
    --> /Users/mschulte/.cargo/registry/src/index.crates.io-6f17d22bba15001f/diesel-2.1.5/src/query_dsl/mod.rs:1542:15
     |
1540 |     fn load<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
     |        ---- required by a bound in this associated function
1541 |     where
1542 |         Self: LoadQuery<'query, Conn, U>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::load`

error[E0271]: type mismatch resolving `<Sqlite as SqlDialect>::SelectStatementSyntax == NotSpecialized`
    --> src/repository/transaction_repository.rs:190:43
     |
190  |             .load::<(Option<BigDecimal>)>(conn).unwrap();
     |              ----                         ^^^^ expected `NotSpecialized`, found `AnsiSqlSelectStatement`
     |              |
     |              required by a bound introduced by this call
     |
     = note: required for `SelectStatement<FromClause<schema::transactions::table>, diesel::query_builder::select_clause::SelectClause<expression::ops::numeric::Mul<functions::aggregate_folding::sum::sum<diesel::sql_types::BigInt, schema::transactions::columns::value>, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Numeric>, BigDecimal>>>>` to implement `QueryFragment<Sqlite>`
     = note: required for `SelectStatement<FromClause<schema::transactions::table>, diesel::query_builder::select_clause::SelectClause<expression::ops::numeric::Mul<functions::aggregate_folding::sum::sum<diesel::sql_types::BigInt, schema::transactions::columns::value>, expression::bound::Bound<diesel::sql_types::Nullable<diesel::sql_types::Numeric>, BigDecimal>>>>` to implement `LoadQuery<'_, _, std::option::Option<BigDecimal>>`
note: required by a bound in `diesel::RunQueryDsl::load`
    --> /Users/mschulte/.cargo/registry/src/index.crates.io-6f17d22bba15001f/diesel-2.1.5/src/query_dsl/mod.rs:1542:15
     |
1540 |     fn load<'query, U>(self, conn: &mut Conn) -> QueryResult<Vec<U>>
     |        ---- required by a bound in this associated function
1541 |     where
1542 |         Self: LoadQuery<'query, Conn, U>,
     |               ^^^^^^^^^^^^^^^^^^^^^^^^^^ required by this bound in `RunQueryDsl::load`

I had different ideas what the error might be, but wasn't able to fix them myself.

• The trait bound BigDecimal: ToSql<diesel::sql_types::Numeric, Sqlite> was not satisfied, however, according to the notes, ToSql<diesel::sql_types::Nullable<diesel::sql_types::Numeric>, __DB> is implemented
  • Is the problem the Nullable? How would I make the sum Nullable? Wrapping the BigDecimal in Some didn't help either
  • Is the problem the Sqlite vs __DB difference? What would I do about that?
• Is the problem that transactions::value is of type BigInt? The error does not suggest this to me, but you never know. Anyways, replacing BigDecimal with something integer-like did not help either

Btw., I have the features numeric and bigdecimal activated.

Anyways, crazy stuff you are doing with Rust, I have no idea how you pull it off 😆

[weiznich]

Hmm, that's strange that it does not work. I did a quick local experiment an I can confirm that it does indeed not work with BigDecimal, although it works just fine with i64/BigInt as return type. Neither of the two trait bounds you listed seems to be the problematic ones. Maybe it's related to #3961. (I do currently not have the capacity to investigate this any further). For reference the following Integer based version compiles fine:

use diesel::prelude::*;

table! {
    transactions(id) {
        id -> Integer,
        value -> Integer,
    }
}

fn main() {
    let mut conn = SqliteConnection::establish("…").unwrap();

    transactions::table
        .select(diesel::dsl::sum(transactions::value) * -1)
        .load::<Option<i64>>(&mut conn)
        .unwrap();
}

(That works because sum() turns a Integer into a BigInt and not into a Numeric).

Thanks for the super quick response! And it's amazing that this is possible, although I have no idea how I would have figured that out by reading the docs 😅 But that's a different story.

We are always happy to receive contributions to the documentation that adds these information where you would have expected them. It's hard for us maintainers to write this kind of documentation as we don't know where users expect to find stuff.

[marcoschulte]

Indeed, your example is working if value is of type Integer. Unfortunately, in my case, I need to use BigInt, and that does not seem to work. Multiplying by -1 or -1i64 leads to the two different error messages:

error[E0277]: the trait bound `{integer}: diesel::Expression` is not satisfied
  --> src/repository/foo.rs:15:55
   |
15 |         .select(diesel::dsl::sum(transactions::value) * -1)
   |                                                       ^ the trait `diesel::Expression` is not implemented for `{integer}`, which is required by `functions::aggregate_folding::sum::sum<diesel::sql_types::BigInt, foo::transactions::columns::value>: std::ops::Mul<_>`
   |
   = help: the following other types implement trait `diesel::Expression`:

and

error[E0277]: the trait bound `i64: diesel::Expression` is not satisfied
  --> src/repository/foo.rs:15:55
   |
15 |         .select(diesel::dsl::sum(transactions::value) * -1i64)
   |                                                       ^ the trait `diesel::Expression` is not implemented for `i64`, which is required by `functions::aggregate_folding::sum::sum<diesel::sql_types::BigInt, foo::transactions::columns::value>: std::ops::Mul<_>`
   |

Regarding the docs, what would you think about creating a cheatsheet (as a new chapter under guide) for now? It could simply start as a collection of random snippets how to perform more complex queries. Maybe it's supported by a cheatsheet testcase in the examples folder which proves that those snippets are indeed compiling and working.
If you think that would help, I could browse through my queries, the ones in the diesel example folder and some github tickets, collect some interesting queries and create a first proposal.

[weiznich]

Indeed, your example is working if value is of type Integer. Unfortunately, in my case, I need to use BigInt, and that does not seem to work. Multiplying by -1 or -1i64 leads to the two different error messages:

At least that's expected. It would need to be a BigDecimal value on the right side to get this working. I guess that there is some missing impl that needs to be tracked down and added to make it actual compile.

Regarding the docs, what would you think about creating a cheatsheet (as a new chapter under guide) for now? It could simply start as a collection of random snippets how to perform more complex queries. Maybe it's supported by a cheatsheet testcase in the examples folder which proves that those snippets are indeed compiling and working.
If you think that would help, I could browse through my queries, the ones in the diesel example folder and some github tickets, collect some interesting queries and create a first proposal.

At least I plan to have a guide for how the expression system works and a second guide how the result mapping (Queryable) works. I think that are the points where most beginners struggle. Some sort of cheatsheet might only have a limited usefulness as it only shows a subset of possible queries. Basically the expression system allows you to combine ~everything with ~everything as long as the types "match"
These guides are mostly not written yet, because I only have a limited amount of time to work on such stuff. If someone is interested in at least starting the work I'm happy to help bringing it over the finish line.