I wrote a beginner program in Rust that actually is working pretty well, with axum for the web-app and sea-orm to manage the connection to the postgres backend. My struggle is putting together a query, or multiple queries, that can get me the sum of two different columns. The problem is each column has a different filter applied.
So this is a financial transaction tracking app I'm building for myself. I have a table of transactions where there is a column for amount which is of type Decimal, a column for date which is of type NaiveDate, and a column for expense which is of type bool. What this means is transactions that are expenses have true in the expense column, and transactions that are income have false in the expense column.
I am trying to get the sum of the expenses before a certain date, let's call it tomorrow. I am also trying to get the sum of the incomes before tomorrow. Then I need to take these two sums, and subtract the expenses from the incomes. This will give me the total I want to show.
I am trying to run both queries in the same get("/") function because I want this to occur when you go to the home page.
My issue is that I can't get it to compile when I have two separate queries, it says type annotations needed. But when there is only one query, I don't get this error.
Here is my code in 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))
}
Here is the code in the entity file (transactions.rs) for reference:
//! 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 {}
I think the error is in the let income_transaction = and let expense_transaction = sections, but I'm not sure.
