在postgres db中，挣扎着使用sea和axum来获得两列的和，每个列都有唯一的过滤器。

Question

我用Rust编写了一个初学者程序，实际上运行得很好，使用axum为web应用程序和sea管理与postgres后端的连接。我的斗争是把一个查询或多个查询组合在一起，这样我就可以得到两个不同列的总和。问题是每一列都有不同的过滤器应用。这是我为自己开发的金融交易跟踪应用程序。我有一个事务表，其中有一列用于amount (类型为Decimal )，一列用于date (类型为NaiveDate )，一列用于expense (类型为bool )。这意味着开支的事务在费用列中有true，而作为收入的事务在费用列中有false。

我正试图在某一日期之前得到费用之和，我们称之为tomorrow。我还试图在tomorrow之前得到收入之和。然后，我需要计算这两笔款项，并从收入中减去开支。这将给我我想显示的总数。

我试图在同一个get("/")函数中运行这两个查询，因为我希望在您访问主页时出现这种情况。

我的问题是，当我有两个不同的查询时，我无法让它编译，它说type annotations needed。但是，当只有一个查询时，我不会得到这个错误。

这是我在main.rs中的代码

mod flash;

use axum::{
    extract::{Extension, Form, Path, Query},
    http::StatusCode,
    response::Html,
    routing::{get, get_service, post},
    Router, Server,
};
use chrono::{DateTime, Duration, NaiveDate, Utc};
use entity::{tags, transaction_tags, transactions, users};
use flash::{get_flash_cookie, post_response, PostResponse};
use migration::{Condition, Migrator, MigratorTrait};
use sea_orm::{prelude::*, Database, FromQueryResult, QueryOrder, QuerySelect, Set};
use sea_query::Expr;
use serde::{Deserialize, Serialize};
use std::{env, net::SocketAddr};
use std::{iter::Sum, str::FromStr};
use tags::Entity as Tags;
use tera::Tera;
use tower::ServiceBuilder;
use tower_cookies::{CookieManagerLayer, Cookies};
use tower_http::services::ServeDir;
use transaction_tags::Entity as TransactionTags;
use transactions::Entity as Transactions;
use users::Entity as Users;

pub const USER_ID_FOR_TEST: i32 = 1;

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    env::set_var("RUST_LOG", "debug");
    tracing_subscriber::fmt::init();

    dotenv::dotenv().ok();
    let db_url = env::var("DATABASE_URL").expect("DATABASE_URL is not set in .env file");
    let host = env::var("HOST").expect("HOST is not set in .env file");
    let port = env::var("PORT").expect("PORT is not set in .env file");
    let server_url = format!("{}:{}", host, port);

    let conn = Database::connect(db_url)
        .await
        .expect("Database connection failed");
    Migrator::up(&conn, None).await.unwrap();
    let templates = Tera::new(concat!(env!("CARGO_MANIFEST_DIR"), "/templates/**/*"))
        .expect("Tera initialization failed");
    // let state = AppState { templates, conn };

    let app = Router::new()
        .route("/", get(total_transactions).post(create_transaction))
        .route("/:id", get(edit_transaction).post(update_transaction))
        .route("/new", get(new_transaction))
        .route("/delete/:id", post(delete_transaction))
        .route("/list", get(list_transactions))
        .nest(
            "/static",
            get_service(ServeDir::new(concat!(
                env!("CARGO_MANIFEST_DIR"),
                "/static"
            )))
            .handle_error(|error: std::io::Error| async move {
                (
                    StatusCode::INTERNAL_SERVER_ERROR,
                    format!("Unhandled internal error: {}", error),
                )
            }),
        )
        .layer(
            ServiceBuilder::new()
                .layer(CookieManagerLayer::new())
                .layer(Extension(conn))
                .layer(Extension(templates)),
        );

    let addr = SocketAddr::from_str(&server_url).unwrap();
    Server::bind(&addr).serve(app.into_make_service()).await?;

    Ok(())
}

#[derive(Deserialize)]
struct Params {
    page: Option<usize>,
    transactions_per_page: Option<usize>,
}

#[derive(Deserialize, Serialize, Debug, Clone)]
struct FlashData {
    kind: String,
    message: String,
}

async fn list_transactions(
    Extension(ref templates): Extension<Tera>,
    Extension(ref conn): Extension<DatabaseConnection>,
    Query(params): Query<Params>,
    cookies: Cookies,
) -> Result<Html<String>, (StatusCode, &'static str)> {
    let page = params.page.unwrap_or(1);
    let transactions_per_page = params.transactions_per_page.unwrap_or(5);
    let paginator = Transactions::find()
        .order_by_asc(transactions::Column::Date)
        .paginate(conn, transactions_per_page);
    let num_pages = paginator.num_pages().await.ok().unwrap();
    let transacts = paginator
        .fetch_page(page - 1)
        .await
        .expect("could not retrieve transactions");

    let mut ctx = tera::Context::new();
    ctx.insert("transacts", &transacts);
    ctx.insert("page", &page);
    ctx.insert("transactions_per_page", &transactions_per_page);
    ctx.insert("num_pages", &num_pages);

    if let Some(value) = get_flash_cookie::<FlashData>(&cookies) {
        ctx.insert("flash", &value);
    }

    let body = templates
        .render("index.html.tera", &ctx)
        .map_err(|_| (StatusCode::INTERNAL_SERVER_ERROR, "Template error"))?;

    Ok(Html(body))
}

async fn new_transaction(
    Extension(ref templates): Extension<Tera>,
) -> Result<Html<String>, (StatusCode, &'static str)> {
    let ctx = tera::Context::new();
    let body = templates
        .render("new.html.tera", &ctx)
        .map_err(|_| (StatusCode::INTERNAL_SERVER_ERROR, "Template error"))?;

    Ok(Html(body))
}

async fn create_transaction(
    Extension(ref conn): Extension<DatabaseConnection>,
    form: Form<transactions::Model>,
    mut cookies: Cookies,
) -> Result<PostResponse, (StatusCode, &'static str)> {
    let model = form.0;

    transactions::ActiveModel {
        date: Set(model.date.to_owned()),
        amount: Set(model.amount.to_owned()),
        expense: Set(model.expense.to_owned()),
        note: Set(model.note.to_owned()),
        user_id: Set(model.user_id.to_owned()),
        ..Default::default()
    }
    .save(conn)
    .await
    .expect("could not insert transaction");

    let data = FlashData {
        kind: "success".to_owned(),
        message: "Transaction successfully added".to_owned(),
    };

    Ok(post_response(&mut cookies, data))
}

async fn edit_transaction(
    Extension(ref templates): Extension<Tera>,
    Extension(ref conn): Extension<DatabaseConnection>,
    Path(id): Path<i32>,
) -> Result<Html<String>, (StatusCode, &'static str)> {
    let transaction: transactions::Model = Transactions::find_by_id(id)
        .one(conn)
        .await
        .expect("could not find transaction")
        .unwrap();

    let mut ctx = tera::Context::new();
    ctx.insert("transaction", &transaction);

    let body = templates
        .render("edit.html.tera", &ctx)
        .map_err(|_| (StatusCode::INTERNAL_SERVER_ERROR, "Template error"))?;

    Ok(Html(body))
}

async fn update_transaction(
    Extension(ref conn): Extension<DatabaseConnection>,
    Path(id): Path<i32>,
    form: Form<transactions::Model>,
    mut cookies: Cookies,
) -> Result<PostResponse, (StatusCode, &'static str)> {
    let model = form.0;

    transactions::ActiveModel {
        id: Set(id),
        date: Set(model.date.to_owned()),
        amount: Set(model.amount.to_owned()),
        expense: Set(model.expense.to_owned()),
        note: Set(model.note.to_owned()),
        user_id: Set(model.user_id.to_owned()),
    }
    .save(conn)
    .await
    .expect("could not edit transaction");

    let data = FlashData {
        kind: "success".to_owned(),
        message: "Transaction successfully updated".to_owned(),
    };

    Ok(post_response(&mut cookies, data))
}

async fn delete_transaction(
    Extension(ref conn): Extension<DatabaseConnection>,
    Path(id): Path<i32>,
    mut cookies: Cookies,
) -> Result<PostResponse, (StatusCode, &'static str)> {
    let transaction: transactions::ActiveModel = Transactions::find_by_id(id)
        .one(conn)
        .await
        .unwrap()
        .unwrap()
        .into();

    transaction.delete(conn).await.unwrap();

    let data = FlashData {
        kind: "success".to_owned(),
        message: "Transaction successfully deleted".to_owned(),
    };

    Ok(post_response(&mut cookies, data))
}

#[derive(Deserialize)]
struct UserParams {
    user_id: i32,
    todays_date: Date,
    tomorrow: Date,
}

#[derive(Deserialize, FromQueryResult)]
struct SumResult {
    sum: Decimal,
}

async fn total_transactions(
    Extension(ref templates): Extension<Tera>,
    Extension(ref conn): Extension<DatabaseConnection>,
) -> Result<Html<String>, (StatusCode, &'static str)> {
    let user_1 = UserParams {
        user_id: 1,
        todays_date: Utc::now().naive_local().date(),
        tomorrow: Utc::now().naive_local().date() + Duration::days(1),
    };
    let expense_transaction = Transactions::find()
        .filter(
            Condition::all()
                .add(transactions::Column::Date.lt(user_1.tomorrow))
                .add(transactions::Column::Expense.eq(true)),
        )
        .select_only()
        .column_as(Expr::col(transactions::Column::Amount).sum(), "expense_sum")
        .one(conn)
        .await
        .unwrap()
        .unwrap()
        .into();

    let expense_sum = expense_transaction.expense_sum;

    let income_transaction = Transactions::find()
        .filter(
            Condition::all()
                .add(transactions::Column::Date.lt(user_1.tomorrow))
                .add(transactions::Column::Expense.eq(false)),
        )
        .select_only()
        .column_as(Expr::col(transactions::Column::Amount).sum(), "income_sum")
        .one(conn)
        .await
        .unwrap()
        .unwrap()
        .into();

    let income_sum = income_transaction.income_sum;

    let total = income_sum - expense_sum;

    let mut ctx = tera::Context::new();
    ctx.insert("user_id", &user_1.user_id);
    ctx.insert("today", &user_1.todays_date);
    ctx.insert("sum", &total);

    let body = templates
        .render("total.html.tera", &ctx)
        .map_err(|_| (StatusCode::INTERNAL_SERVER_ERROR, "Template error"))?;

    Ok(Html(body))
}

下面是实体文件(transactions.rs)中的代码以供参考：

//! SeaORM Entity. Generated by sea-orm-codegen 0.9.1

use sea_orm::entity::prelude::*;
use serde::{Deserialize, Serialize};

#[derive(Clone, Debug, PartialEq, DeriveEntityModel, Deserialize, Serialize)]
#[sea_orm(table_name = "transactions")]
pub struct Model {
    #[sea_orm(primary_key)]
    #[serde(skip_deserializing)]
    pub id: i32,
    pub date: Date,
    #[sea_orm(column_type = "Decimal(Some((14, 4)))")]
    pub amount: Decimal,
    pub expense: bool,
    pub note: Option<String>,
    pub user_id: i32,
}

#[derive(Copy, Clone, Debug, EnumIter, DeriveRelation)]
pub enum Relation {
    #[sea_orm(
        belongs_to = "super::users::Entity",
        from = "Column::UserId",
        to = "super::users::Column::Id",
        on_update = "Cascade",
        on_delete = "Cascade"
    )]
    Users,
    #[sea_orm(has_many = "super::transaction_tags::Entity")]
    TransactionTags,
}

impl Related<super::users::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::Users.def()
    }
}

impl Related<super::transaction_tags::Entity> for Entity {
    fn to() -> RelationDef {
        Relation::TransactionTags.def()
    }
}

impl ActiveModelBehavior for ActiveModel {}

我认为错误出现在let income_transaction =和let expense_transaction =部分，但我不确定。

Answer 1

我想我们在SeaQL不和谐服务器上谈过了吗？

试试这个：

#[derive(FromQueryResult)]
struct GroupResult {
    sum: Decimal,
}

let expense_transaction = Transactions::find()
    .filter(
        Condition::all()
            .add(transactions::Column::Date.lt(user_1.tomorrow))
            .add(transactions::Column::Expense.eq(true)),
    )
    .select_only()
    .column_as(Expr::col(transactions::Column::Amount).sum(), "sum")
    .into_model::<GroupResult>()
    .one(conn)
    .await
    .unwrap();

let expense_sum = expense_transaction.sum;

let income_transaction = Transactions::find()
    .filter(
        Condition::all()
            .add(transactions::Column::Date.lt(user_1.tomorrow))
            .add(transactions::Column::Expense.eq(false)),
    )
    .select_only()
    .column_as(Expr::col(transactions::Column::Amount).sum(), "sum")
    .into_model::<GroupResult>()
    .one(conn)
    .await
    .unwrap();

let income_sum = income_transaction.sum;

相关文档可在https://www.sea-ql.org/SeaORM/docs/advanced-query/custom-select/#handling-custom-selects上找到