From c64d43552497d46ae30272d5bf93c2162ea2a1db Mon Sep 17 00:00:00 2001
From: Physick <96335032+DegustatorPonos@users.noreply.github.com>
Date: Sat, 29 Nov 2025 21:05:36 +0500
Subject: Filtered query generator

---
 :w                  | 66 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 DAL/DB/CRUD.go      | 36 +++++++++++++++++++++++++----
 DAL/DB/CRUD_test.go | 16 +++++++++++--
 3 files changed, 112 insertions(+), 6 deletions(-)
 create mode 100644 :w

diff --git a/:w b/:w
new file mode 100644
index 0000000..d7a5759
--- /dev/null
+++ b/:w
@@ -0,0 +1,66 @@
+package db
+
+import (
+	"fmt"
+	"reflect"
+	"strings"
+)
+
+type DataTable[T any] struct {
+	TableName string
+}
+
+// Filters are stored as KVP where the field is the key and the value is the filter
+func (base *DataTable[T]) formQueryWithFilters(filters map[string]any) string {
+	if len(filters) == 0 {
+		return base.formSelectAllQuery()
+	}
+	var sb = strings.Builder{}
+	var baseQuery = base.formSelectAllQuery()
+	sb.WriteString(strings.TrimRight(baseQuery, ";"))
+	sb.WriteString(" WHERE (")
+	var isFirst = true
+	for k, v := range filters {
+		if !isFirst {
+			sb.WriteString(" AND ")
+		}
+		switch v.(type) {
+		case string:
+			sb.WriteString(fmt.Sprintf("%s = '%s'", k, v))
+		}
+		isFirst = false
+	}
+	sb.WriteString(");")
+	return sb.String()
+}
+
+// Forms a query to get all the fields from database
+func (base *DataTable[T]) formSelectAllQuery() string {
+	var sb = strings.Builder{}
+	sb.WriteString("SELECT (")
+	var fields = base.getFields()
+	for i, v := range fields {
+		if i != 0 {
+			sb.WriteString(", ")
+		}
+		sb.WriteString(v)
+	}
+	sb.WriteString(") FROM ")
+	sb.WriteString(base.TableName)
+	sb.WriteString(";")
+	return sb.String()
+}
+
+func (base *DataTable[T]) getFields() []string {
+	var zero T
+	var outp = make([]string, 0)
+	for _, f := range reflect.VisibleFields(reflect.TypeOf(zero)) {
+		var tag, exists = f.Tag.Lookup("sql")
+		if exists {
+			outp = append(outp, tag)
+		} else {
+			outp = append(outp, f.Name)
+		}
+	}
+	return outp
+}
diff --git a/DAL/DB/CRUD.go b/DAL/DB/CRUD.go
index 51ef908..b466d3c 100644
--- a/DAL/DB/CRUD.go
+++ b/DAL/DB/CRUD.go
@@ -1,6 +1,7 @@
 package db
 
 import (
+	"fmt"
 	"reflect"
 	"strings"
 )
@@ -9,11 +10,38 @@ type DataTable[T any] struct {
 	TableName string
 }
 
+// Filters are stored as KVP where the field is the key and the value is the filter
+func (base *DataTable[T]) formQueryWithFilters(filters map[string]any) string {
+	if len(filters) == 0 {
+		return base.formSelectAllQuery()
+	}
+	var sb = strings.Builder{}
+	var baseQuery = base.formSelectAllQuery()
+	sb.WriteString(strings.TrimRight(baseQuery, ";"))
+	sb.WriteString(" WHERE (")
+	var isFirst = true
+	for k, v := range filters {
+		if !isFirst {
+			sb.WriteString(" AND ")
+		}
+		sb.WriteString(fmt.Sprintf("%s = ", k))
+		switch v.(type) {
+		case string:
+			sb.WriteString(fmt.Sprintf("'%s'", v))
+		case int, uint64:
+			sb.WriteString(fmt.Sprintf("%d", v))
+		}
+		isFirst = false
+	}
+	sb.WriteString(");")
+	return sb.String()
+}
+
 // Forms a query to get all the fields from database
-func (self *DataTable[T]) formSelectRequest() string {
+func (base *DataTable[T]) formSelectAllQuery() string {
 	var sb = strings.Builder{}
 	sb.WriteString("SELECT (")
-	var fields = self.getFields()
+	var fields = base.getFields()
 	for i, v := range fields {
 		if i != 0 {
 			sb.WriteString(", ")
@@ -21,12 +49,12 @@ func (self *DataTable[T]) formSelectRequest() string {
 		sb.WriteString(v)
 	}
 	sb.WriteString(") FROM ")
-	sb.WriteString(self.TableName)
+	sb.WriteString(base.TableName)
 	sb.WriteString(";")
 	return sb.String()
 }
 
-func (self *DataTable[T]) getFields() []string {
+func (base *DataTable[T]) getFields() []string {
 	var zero T
 	var outp = make([]string, 0)
 	for _, f := range reflect.VisibleFields(reflect.TypeOf(zero)) {
diff --git a/DAL/DB/CRUD_test.go b/DAL/DB/CRUD_test.go
index 474c937..3b60aa0 100644
--- a/DAL/DB/CRUD_test.go
+++ b/DAL/DB/CRUD_test.go
@@ -15,11 +15,23 @@ var testDataTable = DataTable[testDataType] {
 	TableName: "DataTable",
 }
 
-func TestFormSelectRequest(t *testing.T) {
+func TestSelectRequestForm(t *testing.T) {
 	var expected = "SELECT (Id, username, Data) FROM DataTable;"
-	var request = testDataTable.formSelectRequest()
+	var request = testDataTable.formSelectAllQuery()
 	if request != expected {
 		t.Errorf("Incorrect select query. \n Expected: '%s' \n Got:      '%s'", expected, request)
 	}
 }
 
+func TestFilteredSelectRequestForm(t *testing.T) {
+	var expected = "SELECT (Id, username, Data) FROM DataTable WHERE (Id = 10 AND username = 'physick');"
+	var request = testDataTable.formQueryWithFilters(map[string]any{
+		"Id": 10,
+		"username": "physick",
+	})
+	if request != expected {
+		t.Errorf("Incorrect select query. \n Expected: '%s' \n Got:      '%s'", expected, request)
+	}
+}
+
+
-- 
cgit v1.3