lib/sql: refactor WhereFields to join where condition with holder as is

When binding parameter with BindWhere, the first parameter will be joined
with the holder. For example, BindWhere("colname>=", p) will result in
"colname>=$1".

While at it, set offset based on [Meta.kind].

2 files changed, 22 insertions, 19 deletions

diff --git a/lib/sql/meta.go b/lib/sql/meta.go
index 487f318a..513991cc 100644
--- a/lib/sql/meta.go
+++ b/lib/sql/meta.go
@@ -75,8 +75,9 @@ func (meta *Meta) Bind(colName string, val any) {
 }
 
 // BindWhere bind value for where condition.
-// The cond string is optional, can be a column name or any text like
-// "and col".
+//
+// The cond string is optional, can be a column name with operator or any
+// text like "AND col=" or "OR col=".
 //
 // It return the length of [Meta.ListHolder].
 //
@@ -164,7 +165,7 @@ func (meta *Meta) UpdateValues() (listVal []any) {
 	return listVal
 }
 
-// WhereFields merge the ListWhereCond and ListHolder separated by "=".
+// WhereFields merge the ListWhereCond and ListHolder.
 //
 // It will return an empty string if kind is DML INSERT.
 func (meta *Meta) WhereFields() string {
@@ -173,17 +174,19 @@ func (meta *Meta) WhereFields() string {
 	}
 
 	var (
-		off = len(meta.ListValue)
-
-		sb strings.Builder
-		x  int
+		off int
+		sb  strings.Builder
+		x   int
 	)
 
+	if meta.kind == DMLKindUpdate || meta.kind == DMLKindInsert {
+		off = len(meta.ListValue)
+	}
 	for ; x < len(meta.ListWhereCond); x++ {
 		if x > 0 {
 			sb.WriteByte(' ')
 		}
-		fmt.Fprintf(&sb, `%s=%s`, meta.ListWhereCond[x], meta.ListHolder[off+x])
+		fmt.Fprintf(&sb, `%s%s`, meta.ListWhereCond[x], meta.ListHolder[off+x])
 	}
 	return sb.String()
 }
diff --git a/lib/sql/meta_example_test.go b/lib/sql/meta_example_test.go
index 8094b814..724a432d 100644
--- a/lib/sql/meta_example_test.go
+++ b/lib/sql/meta_example_test.go
@@ -181,8 +181,8 @@ func ExampleMeta_UpdateFields() {
 
 	meta.Bind(`id`, t.ID)
 	meta.Bind(`name`, t.Name)
-	meta.BindWhere(`id`, qid)
-	meta.BindWhere(`AND name`, qname)
+	meta.BindWhere(`id=`, qid)
+	meta.BindWhere(`AND name=`, qname)
 
 	var q = fmt.Sprintf(`UPDATE t SET %s WHERE %s;`, meta.UpdateFields(), meta.WhereFields())
 
@@ -242,8 +242,8 @@ func ExampleMeta_UpdateValues() {
 func ExampleMeta_WhereFields() {
 	var meta = sql.NewMeta(sql.DriverNamePostgres, sql.DMLKindSelect)
 
-	meta.BindWhere(`id`, 1000)
-	meta.BindWhere(`AND name`, `share`)
+	meta.BindWhere(`id=`, 1000)
+	meta.BindWhere(`AND name=`, `share`)
 
 	fmt.Printf(`SELECT * FROM t WHERE %s;`, meta.WhereFields())
 	// Output:
@@ -273,8 +273,8 @@ func ExampleMeta_deleteOnPostgresql() {
 		qname = `hello`
 	)
 
-	meta.BindWhere(`id`, qid)
-	meta.BindWhere(`OR name`, qname)
+	meta.BindWhere(`id=`, qid)
+	meta.BindWhere(`OR name=`, qname)
 
 	var q = fmt.Sprintf(`DELETE FROM t WHERE %s;`, meta.WhereFields())
 
@@ -332,21 +332,21 @@ func ExampleMeta_selectOnPostgresql() {
 
 	meta.Bind(`id`, &t.ID)
 	meta.Bind(`name`, &t.Name)
-	meta.BindWhere(`id`, qid)
-	meta.BindWhere(`name`, qname)
+	meta.BindWhere(`id=`, qid)
+	meta.BindWhere(`OR name=`, qname)
 
-	var q = fmt.Sprintf(`SELECT %s FROM t WHERE id=$1 OR name=$2;`, meta.Names())
+	var q = fmt.Sprintf(`SELECT %s FROM t WHERE %s;`, meta.Names(), meta.WhereFields())
 
 	// db.QueryRow(q, meta.ListWhereValue...).Scan(meta.ListValue...)
 
 	fmt.Println(q)
+	fmt.Println(`WHERE=`, meta.ListWhereValue)
 	fmt.Println(len(meta.ListValue))
-	fmt.Println(meta.ListWhereValue)
 
 	// Output:
 	// SELECT id,name FROM t WHERE id=$1 OR name=$2;
+	// WHERE= [1 hello]
 	// 2
-	// [1 hello]
 }
 
 // Sometime the query need to be stiched piece by piece.