英文:
Parameterize sql query in go
问题
我有一个简单的查询，如下所示。
select json_agg(row_to_json(t)) from (select *
from jobs, companies, locations
where jobs.company_id = companies.id and jobs.location_id = locations.id
$extra
and to_tsvector(jobs.name || ' ' || companies.name || ' ' || locations.name) @@ to_tsquery($1)
and to_tsvector(locations.name) @@ to_tsquery($2)
limit $3) t
但是它在附加查询方面存在一些问题。$1 和 $2 参数是根据查询用户的请求可选的。但是，在这种情况下，to_tsquery（postgresql）不支持任何"match all"查询，比如'*'，所以我们必须有整个附加查询 and to_tsvector(locations.name) @@ to_tsquery($2)，要么带有适当的参数，要么根本没有。
这使得基于参数构建查询变得繁琐，因为我们必须一直复制相同的查询并添加附加查询，而且很容易累积。我有一个解决方案，可以使用strings.Replace来添加附加查询，但是如果需要，我们仍然需要转义参数。是否有其他解决方案可以以清晰的方式完成这个任务？
英文:
I have a simple query like the one below.
select json_agg(row_to_json(t)) from (select *
from jobs, companies, locations
where jobs.company_id = companies.id and jobs.location_id = locations.id
$extra
and to_tsvector(jobs.name || ' ' || companies.name || ' ' || locations.name) @@ to_tsquery($1)
and to_tsvector(locations.name) @@ to_tsquery($2)
limit $3) t
But it has some problems regarding additional query. The $1 and $2 parameters is optional based on the query user requests. But still, at least in this case to_tsquery (postgresql) doesn't support any "match all" query like '*' so we must have the whole additional query and to_tsvector(locations.name) @@ to_tsquery($2) with proper parameter or not at all.
This makes building query based on parameter tiresome work since we have to copy same query and add additional query all the time, and it's easily add up. I have a solution in mind to use strings.Replace to add up additional queries, but still we need to escape the parameter if needed. Is there any other solution can get this done in a clean way ?
答案1
得分: 4
以下是翻译好的内容：
这里是一个巧妙的解决方案，希望能对你有所帮助。这个查询示例使用了github.com/lib/pq的语法查询。
baseQuery := `
SELECT *
FROM
tableName
WHERE
(name ILIKE $1)`
params := []interface{}{"%" + nameLike + "%"}
// 你可以使用这种方式添加可选条件
if condition == true {
baseQuery += fmt.Sprintf(` AND student_no=$%d`, len(params)+1)
params = append(params, studentNo)
}
filterQuery := baseQuery + ` ORDER BY contract_no ASC`
if totalLimit != 0 {
filterQuery += fmt.Sprintf(` LIMIT $%d OFFSET $%d`, len(params)+1, len(params)+2)
params = append(params, totalLimit, totalSkip)
}
rows, err := db.Query(filterQuery, params...)
希望对你有所帮助！
英文:
Here the tricky solution, hope this can help you. this query example using syntax query for github.com/lib/pq
baseQuery := `
SELECT *
FROM
tableName
WHERE
(name ILIKE $1)`
params := []interface{}{"%" + nameLike + "%"}
// you can add optional criteria using this way
if condition==true {
baseQuery += fmt.Sprintf(` AND student_no=$%d`, len(params)+1)
params = append(params, studentNo)
}
filterQuery := baseQuery + ` ORDER BY contract_no ASC`
if totalLimit != 0 {
filterQuery += fmt.Sprintf(` LIMIT $%d OFFSET $%d`, len(params)+1, len(params)+2)
params = append(params, totalLimit, totalSkip)
}
rows, err := db.Query(filterQuery, params...)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论