英文:
SQLC named parameter in generated code for sql containing conditional where
问题
我正在使用golang的SQLC从SQL生成CRUD操作的go代码。
我的选择查询如下所示:
-- name: SearchProducts :many
SELECT * FROM product
WHERE ( $1::varchar(100) IS NULL OR name LIKE '%$1%' )
AND ( $2::varchar(1000) IS NULL OR description LIKE '%$2%' );
SQLC生成的代码如下所示:
type SearchProductsParams struct {
Column1 string `json:"column_1"`
Column2 string `json:"column_2"`
}
func (q *Queries) SearchProducts(ctx context.Context, arg SearchProductsParams) ([]Product, error) {
rows, err := q.db.QueryContext(ctx, searchProducts, arg.Column1, arg.Column2)
if err != nil {
return nil, err
}
....
是否有办法配置SQLC,使其在SearchProductsParams
结构体中使用name
和description
而不是Column1
和Column2
?
英文:
I am using golang SQLC to generate CRUD operation go code from sql.
My select query is like bellow
-- name: SearchProducts :many
SELECT * FROM product
WHERE ( $1::varchar(100) IS NULL OR name LIKE '%$1%' )
AND ( $2::varchar(1000) IS NULL OR description LIKE '%$2%' );
SQLC generating code like bellow
type SearchProductsParams struct {
Column1 string `json:"column_1"`
Column2 string `json:"column_2"`
}
func (q *Queries) SearchProducts(ctx context.Context, arg SearchProductsParams) ([]Product, error) {
rows, err := q.db.QueryContext(ctx, searchProducts, arg.Column1, arg.Column2)
if err != nil {
return nil, err
}
....
Is there any way to configure sqlc so that it will use name & description instead of Column1 & Column2 in SearchProductsParams struct
答案1
得分: 1
你可以使用命名参数来进行查询。
-- name: SearchProducts :many
SELECT * FROM product
WHERE ( sqlc.arg(email)::varchar(100) IS NULL OR name LIKE '%sqlc.arg(email)%' )
AND ( sqlc.arg(description)::varchar(1000) IS NULL OR description LIKE '%sqlc.arg(description)%' );
你可以在这里找到相关文档。
在使用条件过滤进行查询时,以下方法对我有效:
SELECT * FROM product
WHERE
email like CASE
WHEN @filter_email::bool
THEN @email::VARCHAR
ELSE '%'
END
AND
description = CASE
WHEN @filter_description::bool
THEN @description::VARCHAR
ELSE description
END
你需要使用filter_email或filter_description标志以及实际进行过滤的值。
英文:
You can use named parameters
-- name: SearchProducts :many
SELECT * FROM product
WHERE ( sqlc.arg(email)::varchar(100) IS NULL OR name LIKE '%sqlc.arg(email)%' )
AND ( sqlc.arg(description)::varchar(1000) IS NULL OR description LIKE '%sqlc.arg(description)%' );
You can find the documentation here
When doing selects with conditional filters what it works for me was something like this:
SELECT * FROM product
WHERE
email like CASE
WHEN @filter_email::bool
THEN @email::VARCHAR
ELSE '%'
END
AND
description = CASE
WHEN @filter_description::bool
THEN @description::VARCHAR
ELSE description
END
You have to use the flag filter_email or filter_description flag and the value when you actually want to filter.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论