Support for building dynamic queries?

[railsmechanic]

Is there any builtin support to build e.g. dynamic UPDATE queries?
I found the sqlx.Named function which is able to take a map and assign the values according to the keys in the query, but is it possible with sqlx to build a (whole) query based on a given map?

Currently I'm building the query this way:

...

params := map[string]interface{}{
    "name": "John",
    "age":32,
}

named := make([]string, 0, len(params))
for key := range params {
  named = append(named, fmt.Sprintf("%s=:%s", key, key))
}
params["id"] = 232

result, err := db.NamedExec(fmt.Sprintf("UPDATE users SET %s WHERE id=:id;", strings.Join(named, ", ")), params)

...

Many thanks for your help...

[jmoiron]

I've tried to mostly stay out of the business of building queries because

1. I suspect that's an orthogonal concern
2. There are other packages devoted entirely to it
3. It seems opinionated enough what goes into it where choice would be good

The code above is fine; it's a pretty small utility function whose behavior is very straight forward.

[pkieltyka]

I recommend https://github.com/lann/squirrel for this

[jmoiron]

I can second squirrel, and there are likely other packages that will focus on this in future.

[YangShuting]

I just met this problem and solved it. Below is my solution, hope help you.
You can use go variadic parameter: A parameter type prefixed with three dots (...) is called a variadic parameter.
Here is the dynamic select query spired me: https://stackoverflow.com/questions/46859705/making-dynamic-queries-in-golang-and-mysql
As the dynamic update query, well, string concat will help. Here is the code:

`func (um UserMapper) updateAUserInfo(res http.ResponseWriter, req http.Request){
decoder := json.NewDecoder(req.Body)
var user User
err := decoder.Decode(&user)
if err != nil{
panic(err)
}
// EXMP: "email = '%s', name = '%s', password = '%s'"
var params string
//EXMP: "hly066@gmail.com", "hly*", "hly***"
var values []interface{}
v := reflect.ValueOf(user)
for i := 0; i < v.NumField(); i++{
key := v.Type().Field(i).Name
value := v.Field(i).Interface()
if value != "" && value != nil && key != "CreatedAt" && key != "ID"{
kString := key + "='%s'"
params = kString + "," + params
values = append(values, value)
}
}
params = params[:(len(params)-1)]
updatedUser, err := um.updateUserInfo(params, values, user.ID)
fmt.Fprintf(res, "updated info are: %v", updatedUser)

}`

And the updateUserInfo function will be:
func (um *UserMapper) updateUserInfo(params string, values []interface{}, id string) (*User, error) { u := &User{} paramString := "UPDATE users set " + params err := um.DB.QueryRow(fmt.Sprintf(fmt.Sprintf(paramString, values...) + fmt.Sprintf(" where id = '%s'", id) )) if err != nil{ log.Fatal(err) } return u, nil }

The point is how to concat the values... and id value, I concat the two fmt.Sprintf() to solved =>
err := um.DB.QueryRow(fmt.Sprintf(fmt.Sprintf(paramString, values...) + fmt.Sprintf(" where id = '%s'", id) ))
Hope help you.

[rafgugi]

@YangShuting please use appropriate use of markdown https://github.com/adam-p/markdown-here/wiki/Markdown-Cheatsheet#code

[ealves-pt]

In case someone struggled to understand the code snipped above here it is:

func (um UserMapper) updateAUserInfo(res http.ResponseWriter, req http.Request) {
	decoder := json.NewDecoder(req.Body)
	var user User
	err := decoder.Decode(&user)
	if err != nil {
		panic(err)
	}

	// EXMP: "email = '%s', name = '%s', password = '%s'"
	var params string

	//EXMP: "hly066@gmail.com", "hly*", "hly***"
	var values []interface{}
	v := reflect.ValueOf(user)

	for i := 0; i < v.NumField(); i++ {
		key := v.Type().Field(i).Name
		value := v.Field(i).Interface()
		if value != "" && value != nil && key != "CreatedAt" && key != "ID" {
			kString := key + "='%s'"
			params = kString + "," + params
			values = append(values, value)
		}
	}
	
	params = params[:(len(params) - 1)]
	updatedUser, err := um.updateUserInfo(params, values, user.ID)
	fmt.Fprintf(res, "updated info are: %v", updatedUser)
}

As option, you can use COALESCE. So, your sql will look something like this:

SELECT *
FROM products
WHERE COALESCE(:category_id, category_id)