英文:
Is it possible to omit field in where clause in postgresql?
问题
在SQL中，我有以下代码：
-- name: FilterRecords :many
SELECT *
FROM records
WHERE industry_id = $3 and region_code = $4 and city_code = $5
OFFSET $1 LIMIT $2;
我想要实现的目标是，如果提供的 industry_id 值为空字符串，则在 WHERE 子句中排除它。因为当我进行查询时，它会计算空字符串，但我希望如果它为空并且只选择与 region_code 和 city_code 匹配的记录。
英文:
In SQL I have following code:
-- name: FilterRecords :many
SELECT *
FROM records
WHERE industry_id = $3 and region_code = $4 and city_code = $5
OFFSET $1 LIMIT $2;
What I'm trying to achieve is to exclude industry_id from where clause if provided value for it is blank string. Because when I'm making query it's counting blank strings, but I want to omit it if it's blank and select only records where they match region_code and city_code.
答案1
得分: 0
你可以使用Golang的默认text/template来实现这个。
variables := map[string]interface{}{
"industry_id": ...,
"region_id": ...,
"city_code": ...,
"offset": ...,
"limit": ...,
}
qs := new(strings.Builder)
template.Must(template.New("qt").Parse(`
SELECT *
FROM records where
{{ if .industry_id -}} industry_id = {{.industry_id}} and {{- end }}
region_code = {{.region_code}} and city_code = {{.city_code}}
OFFSET {{.offset}} LIMIT {{.limit}}`)).Execute(qs, variables)
fmt.Printf("qs: %v\n", qs.String())
英文:
You can do this by golangs default text/template.
variables := map[string]interface{} {
"industry_id": ...,
"region_id": ...,
"city_code": ...,
"offset": ...,
"limit": ...,
}
qs := new(strings.Builder)
template.Must(template.New("qt").Parse(`
SELECT *
FROM records where
{{ if .industry_id-}} industry_id= {{.industry_id}} and {{- end }}
region_code = {{.region_code}} and city_code = {{.city_code}}
OFFSET {{.offset}} LIMIT {{.limit}}`)).
Execute(qs, variables)
fmt.Printf("qs: %v\n", qs.String())
答案2
得分: 0
这是为我定制的一个并且正常工作的。
-- 名称：FilterApz：多个
选择 *
从 apz
其中 industry_id LIKE COALESCE(NULLIF($3, ''), '%%') and region_code = $4 and city_code = $5
偏移 $1 限制 $2;
英文:
This is the one made for me and worked correctly.
-- name: FilterApz :many
SELECT *
FROM apz
WHERE industry_id LIKE COALESCE(NULLIF($3, ''), '%%') and region_code = $4 and city_code = $5
OFFSET $1 LIMIT $2;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论