CODEWITHSUNDEEP
rustrust-diesel
mhutter
mhutter

Reputation: 2916

Diesel: Adding result of subqueries

Given the following tables:

accounts (
  id INTEGER,
  opening_balance INTEGER,
)

transactions (
  debit INTEGER,
  credit INTEGER,
  amount INTEGER

  foreign key debit references accounts (id),
  foreign key credit references accounts (id)
)

I want to execute the following SQL query:

select
  id,
  opening_balance
  + (select sum(amount) from transactions where debit = account_id)
  - (select sum(amount) from transactions where credit = account_id)
from accounts;

I tried something like this:

accounts
    .select((
        id,
        opening_balance
            + transactions::table
                .select(sum(transactions::amount))
                .filter(transactions::debit.eq(id))
            - transactions::table
                .select(sum(transactions::amount))
                .filter(transactions::credit.eq(id)),
    ))

While the individual parts of this query work fine, I cannot get this to compile.

the trait bound 
`diesel::query_builder::SelectStatement<schema::transactions::table, diesel::query_builder::select_clause::SelectClause<aggregate_folding::sum::sum<diesel::sql_types::Integer, schema::transactions::columns::amount>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<schema::transactions::columns::debit, schema::fiscal_year_accounts::columns::account_id>>>: diesel::Expression`
is not satisfied
required because of the requirements on the impl of `AsExpression<diesel::sql_types::Integer>` for 
`diesel::query_builder::SelectStatement<schema::transactions::table, diesel::query_builder::select_clause::SelectClause<aggregate_folding::sum::sum<diesel::sql_types::Integer, schema::transactions::columns::amount>>, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::Eq<schema::transactions::columns::debit, schema::fiscal_year_accounts::columns::account_id>>>`

The + and - operators work with static values, but how can I get them to work with subqueries?

Upvotes: 2

Views: 2051

Answers (1)

weiznich
weiznich

Reputation: 3435

First of all: Always provide a complete minimal example of your problem. This includes the exact version of all used crates, all relevant imports to make your code actually producing this error message, the complete error message with all help and notice statements and in diesels case the generated schema file.

To answer your question: You miss two call to .single_value() which is required to convert a query into a subquery that could be used as expression. Both subqueries return a Nullable<BigInt> therefore it is required that opening_balance as a matching type.

For the sake of completeness see the working code below

#[macro_use]
extern crate diesel;
use diesel::prelude::*;

table! {
    accounts {
        id -> Integer,
        // changed to `BigInt` as a sum of `Integer` returns a `BigInt`
        opening_balance -> BigInt,
    }
}

table! {
    transactions {
        id -> Integer,
        amount -> Integer,
        debit -> Integer,
        credit -> Integer,
    }
}

allow_tables_to_appear_in_same_query!(accounts, transactions);

fn test() {
    use self::accounts::dsl::*;
    use diesel::dsl::sum;

    let _q = accounts.select((
        id,
        opening_balance.nullable() // call `.nullable()` here to explicitly mark it as potential nullable
            + transactions::table
                .select(sum(transactions::amount))
                .filter(transactions::debit.eq(id))
                .single_value() // call `.single_value()` here to transform this query into a subquery
            - transactions::table
                .select(sum(transactions::amount))
                .filter(transactions::credit.eq(id))
                .single_value(), // call `.single_value()` here to transform this query into a subquery
    ));
}

Upvotes: 4

Related Questions