From af1177e814d19e63ce39c42fc7c5888e4b3d9604 Mon Sep 17 00:00:00 2001
From: Joris
Date: Sat, 9 Oct 2021 10:27:45 +0200
Subject: Search by category

---
 src/db/payments.rs | 48 ++++++++++++++++++++++++++++++++++++++----------
 1 file changed, 38 insertions(+), 10 deletions(-)

(limited to 'src/db')

diff --git a/src/db/payments.rs b/src/db/payments.rs
index 35e7f68..624ba9f 100644
--- a/src/db/payments.rs
+++ b/src/db/payments.rs
@@ -39,15 +39,20 @@ INNER JOIN
 WHERE
     payments.deleted_at IS NULL
     AND payments.frequency = ?
+    {}
     {}
         "#,
-        search_query(search.clone())
+        search_query(search.clone()),
+        category_query(payment_query.category)
     );
 
-    let res = bind_search(
-        sqlx::query_as::<_, Count>(&query)
-            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
-        search,
+    let res = bind_category(
+        bind_search(
+            sqlx::query_as::<_, Count>(&query)
+                .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
+            search,
+        ),
+        payment_query.category,
     )
     .fetch_one(pool)
     .await;
@@ -93,18 +98,23 @@ WHERE
     payments.deleted_at IS NULL
     AND payments.frequency = ?
     {}
+    {}
 ORDER BY
     payments.date DESC
 LIMIT ?
 OFFSET ?
         "#,
-        search_query(search.clone())
+        search_query(search.clone()),
+        category_query(payment_query.category)
     );
 
-    let res = bind_search(
-        sqlx::query_as::<_, payment::Table>(&query)
-            .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
-        search,
+    let res = bind_category(
+        bind_search(
+            sqlx::query_as::<_, payment::Table>(&query)
+                .bind(payment_query.frequency.unwrap_or(Frequency::Punctual)),
+            search,
+        ),
+        payment_query.category,
     )
     .bind(per_page)
     .bind(offset)
@@ -159,6 +169,24 @@ fn bind_search<'a, Row: FromRow<'a, SqliteRow>>(
     })
 }
 
+fn category_query(category: Option<i64>) -> String {
+    if category.is_some() {
+        "AND category_id = ?".to_string()
+    } else {
+        "".to_string()
+    }
+}
+
+fn bind_category<'a, Row: FromRow<'a, SqliteRow>>(
+    query: sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>>,
+    category: Option<i64>,
+) -> sqlx::query::QueryAs<'a, Sqlite, Row, SqliteArguments<'a>> {
+    match category {
+        Some(id) => query.bind(id),
+        _ => query,
+    }
+}
+
 pub async fn list_for_stats(pool: &SqlitePool) -> Vec<payment::Stat> {
     let query = r#"
 SELECT
-- 
cgit v1.2.3