Filtered query generator

2 files changed, 46 insertions, 6 deletions

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)
+	}
+}
+
+