我在 Rust 中编写了一个初学者程序，实际上运行良好，使用 axum 用于 Web 应用程序和 sea-orm 来管理与 postgres 后端的连接。我的斗争是将一个或多个查询放在一起，这些查询可以让我得到两个不同列的总和。问题是每列都应用了不同的过滤器。 所以这是我为自己构建的金融交易跟踪应用程序。我有一个交易表，其中有一列用于amount，类型为Decimal，一列用于date的类型为NaiveDate，还有一列用于 bool 类型的expense。这意味着属于支出的交易记录在支出列中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 =部分，但我不确定。
我想我们在 SeaQL Discord 服务器上谈过？
试试这个：
#[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