英文:
Golang UPDATE column with Postgres
问题
假设我有一个名为employments的表和一个名为Employment的结构体。
type Employment struct {ID int `json:"id"`Created_at string `json:"created_at"`Updated_at string `json:"updated_at"`Education string `json:"education"`Job string `json:"job"`Position string `json:"position"`Business_phone string `json:"business_phone"`Next_payday string `json:"next_payday"`Employment_type int `json:"employment_type"`Income float64 `json:"income"`Additional float64 `json:"additional"`}
用户可以更新他们的employment，问题是我不知道用户想要更新哪些字段。
因此，我决定遍历输入的结构体，获取非空字段，以生成查询字符串，类似于UPDATE employments SET position=$1, income=$2 WHERE id=$3。
这是我这次得到的代码：
func FlexibleUpdate(table_name string, str interface{}, cond string, ret string) string {query := "UPDATE " + table_name + " SET "j := 0m := structs.Map(str)for i := range m {if m[i] != "" && m[i] != 0 && m[i] != 0.0 {j++query = query + strings.ToLower(i) + "=$" + strconv.Itoa(j) + ","}}j++// 添加条件if cond != "" {query = query[:len(query)-1] + " WHERE " + cond + "=$" + strconv.Itoa(j)}// 返回值if ret != "" {query = query + " RETURNING " + ret}return query}
我不知道如何将输入值分配给$1, $2, ...以执行查询。
database.DB.QueryRow(query_string, value_1, value_2, ...)
如果你有任何想法或其他解决方法，请告诉我。
英文:
Let's say I have a table employments and a struct Employment
type Employment struct {ID int `json:"id"`Created_at string `json:"created_at"`Updated_at string `json:"updated_at"`Education string `json:"education"`Job string `json:"job"`Position string `json:"position"`Business_phone string `json:"business_phone"`Next_payday string `json:"next_payday"`Employment_type int `json:"employment_type"`Income float64 `json:"income"`Additional float64 `json:"additional"`}
User can update their employment, the problem is I don't know which fields user want to update.
So that, I decided to range over input struct to get non nil fields to generate query string, some thing like UPDATE employments SET position=$1, income=$2 WHERE id=$3
This is what I got this time
func FlexibleUpdate(table_name string, str interface{}, cond string, ret string) string {query := "UPDATE " + table_name + " SET "j := 0m := structs.Map(str)for i := range m {if m[i] != "" && m[i] != 0 && m[i] != 0.0 && {j++query = query + strings.ToLower(i) + "=$" + strconv.Itoa(j) + ","}}j++// adding conditionsif cond != "" {query = query[:len(query)-1] + " WHERE " + cond + "=$" + strconv.Itoa(j)}// return valuesif ret != "" {query = query + " RETURNING " + ret}return query}
I don't know how to assign input values to $1, $2, ... to execute query
database.DB.QueryRow(query_string, value_1, value_2, ...)
Let me know if you have any idea or another way to resolve it.
答案1
得分: 2
只需在切片中收集非nil值，然后在执行查询时使用该切片和...。
var values []interface{}for i := range m {if v := m[i]; v != "" && v != 0 && v != 0.0 && /* 这里你漏掉了一个条件 */ {j++query = query + strings.ToLower(i) + "=$" + strconv.Itoa(j) + ","values = append(values, v)}}// ....database.DB.QueryRow(query_string, values...)
请注意，这只是代码的一部分，可能需要根据上下文进行适当的修改和使用。
英文:
Just collect the non-nil values in a slice and then use that slice with ... when executing the query.
var values []interface{}for i := range m {if v := m[i]; v != "" && v != 0 && v != 0.0 && /* you're missing a condition here */{j++query = query + strings.ToLower(i) + "=$" + strconv.Itoa(j) + ","values = append(values, v)}}// ....database.DB.QueryRow(query_string, values...)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论