英文:
how to loop over fields of a struct for query filtering
问题
我有一个数据库，每一行对应一个具有以下字段的结构体：
type item struct {
ItemId *string `json:"item_id"`
OwnerId *string `json:"owner_id"`
Status *string `json:"status"`
... // 还有很多其他字段
}
在数据库中，所有行的所有字段都已填充。现在我想要一个函数，它以可能未填充字段的item对象作为输入，并返回一个SQL查询字符串。例如：
func FindItems(filter item) string
输入的item用作过滤器。逻辑如下（类似于Python风格）：
query = `select * from item_table`
condition = ""
for field, value in filter:
if value != nil:
condition = " and " if condition else " where "
condition += " field=value"
query += condition
在Go中如何实现这个功能？或者在Go中有更好的过滤方法吗？
英文:
I have a database where each row corresponds to a struct with the following fields
type item struct {
ItemId *string `json:"item_id"`
OwnerId *string `json:"owner_id"`
Status *string `json:"status"`
... // many more
}
Inside the database, all fields are filled for all rows. Now I would like to have a function that takes an item object whose fields may not be filled as input and return a SQL query string. For example
func FindItems(filter item) string
The input item serves as a filter. The logic is as follows (in kind of python style)
query = `select * from item_table`
condition = ""
for field, value in filter:
if value != nil:
condition = " and " if condition else " where "
condition += " field=value"
query += condition
How can I do this in go? Or is there a better way to do filter in go?
答案1
得分: 2
你可以使用reflect包来枚举你的结构体字段和值：
package main
import (
"fmt"
"reflect"
)
type item struct {
ItemID *string `json:"item_id"`
OwnerID *string `json:"owner_id"`
Status *string `json:"status"`
}
func FindItemsQuery(filter item) string {
query := `select * from item_table`
condition := ""
val := reflect.ValueOf(filter)
for i := 0; i < val.NumField(); i++ {
valField := val.Field(i)
if !valField.IsNil() {
if condition != "" {
condition += " and "
} else {
condition += " where "
}
condition += fmt.Sprintf("%s=%v", val.Type().Field(i).Tag.Get("json"), valField.Elem())
}
}
return query + condition
}
func main() {
itemID := "123"
item := item{ItemID: &itemID}
fmt.Println(FindItemsQuery(item)) // select * from item_table where item_id=123
}
请记住，使用额外的json标签，如json:"item_id,omitempty"，会破坏你的查询。你应该考虑使用自定义结构体标签来定义SQL字段的名称。
英文:
You can use reflect package to enumerate your structure fields and values:
<!-- language: go -->
package main
import (
"fmt"
"reflect"
)
type item struct {
ItemID *string `json:"item_id"`
OwnerID *string `json:"owner_id"`
Status *string `json:"status"`
}
func FindItemsQuery(filter item) string {
query := `select * from item_table`
condition := ""
val := reflect.ValueOf(filter)
for i := 0; i < val.NumField(); i++ {
valField := val.Field(i)
if !valField.IsNil() {
if condition != "" {
condition += " and "
} else {
condition += " where "
}
condition += fmt.Sprintf("%s=%v", val.Type().Field(i).Tag.Get("json"), valField.Elem())
}
}
return query + condition
}
func main() {
itemID := "123"
item := item{ItemID: &itemID}
fmt.Println(FindItemsQuery(item)) // select * from item_table where item_id=123
}
Keep in mind that using additional json tags like json:"item_id,omitempty" will break your query. You should consider using custom structure tags to define the names of SQL fields.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论