英文:
Implementing filters in ORM golang
问题
我正在开发一个 API，该 API 接受用于过滤的参数（如下所示）：
/api/endpoint?filter_key_1=filter_value_1&...
我之前在 Spring 上工作过，那里的 Criteria API 允许动态构建 SQL 查询而不需要太多麻烦。在 Golang 中，我正在使用 gorm 处理 ORM 操作。是否有办法在不编写冗余代码的情况下构建具有可选参数的查询？
例如：
如果发送的请求是：
/api/endpoint?fk_1=fv_1&fk_2=fv_2&fk_3=fv_3
生成的查询应为：
select * from table where fk_1 = fv_1 AND fk_2 = fv_2 AND fk_3 = fv_3
但是在以下情况下：
/api/endpoint?fk_1=fv_1
生成的查询应为：
select * from table where fk_1 = fv_1
目前我的方法是检查每个变量是否存在，并构建查询字符串：
query := "select * from table where "
if fk_1 != "" {
query += "fk_1 = fv_1"
}
... 等等
但这种方法似乎非常笨拙且容易出错。
任何帮助将不胜感激！谢谢
编辑
在 @bjornaer 的答案基础上，对我有帮助的是将 map[string][]string 转换为我可以发送给 gorm 的形式，即 map[string]interface{}。
现在不再需要冗余的检查或字符串操作来进行过滤。
英文:
I am working on an api which takes in parameters for filters (as given below)
/api/endpoint?filter_key_1=filter_value_1&...
I've previously worked on spring where the criteria API allows for dynamically building SQL queries without much hassle. In golang I'm using gorm for handling the ORM operations. Is there anyway to build the queries with optional parameters without writing redundant code?.
For example:
If the request sent is:
/api/endpoint?fk_1=fv_1&fk_2=fv_2&fk_3=fv_3
Query generated should be :
select * from table where fk_1 = fv_1 AND fk_2 = fv_2 AND fk_3 = fv_3
but in case of :
/api/endpoint?fk_1=fv_1
Query generated should be:
select * from table where fk_1 = fv_1
Currently my approach is to check if each variable is present and build the query as a string :
query:="select * from table where "
if fk_1 != ""{
query += "fk_1 = fv_1"
}
... and so on
but this seems very awkward and error prone
Any help will be appreciated! Thanks
EDIT
Building on @bjornaer's answer what helped me was to get the map[string][]string in a form that I can send the same to gorm, map[string]interface{}.
This thread will help in the same.
Now there's no longer a need for redundant checks or string operations in the filters
答案1
得分: 2
所以在我看来，你的问题有两个部分：
- 你需要从URL中获取查询参数的值，
- 将它们插入到数据库查询中。
我看不出你是如何处理请求的，所以让我们假设你使用了http包：通过req.URL你可以得到URL对象，然后调用Query()方法可以得到一个map[string][]string类型的查询参数，将其存储在一个名为URLQuery的变量中。让我们先暂停一下，看看如何使用gorm进行查询：
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
QueryFields: true,
})
在这里，我打开了一个sqlite数据库，然后你可以传递一个变量引用来填充你的查询，例如：
result := db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
现在根据上面的示例，将你的变量替换为：
result := db.Where(map[string]interface{}URLQuery).Find(&users)
英文:
so it seems to me your question has 2 parts:
- you need to retrieve your query values from your url and
- insert them to your db query
I don't see how you are handling your requests so let's assume you use the http package: from req.URL you get the URL object and from that calling the Query() method yields a map[string][]string of your query parameters, with those in a variable URLQuery let's pause and look at how you query with gorm:
db, err := gorm.Open(sqlite.Open("gorm.db"), &gorm.Config{
QueryFields: true,
})
here I open a sqlite, then you can pass a variable reference to fill with your query, for example:
result := db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
now from the example above, replace your variable in:
result := db.Where(map[string]interface{}URLQuery).Find(&users)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论