在生成的代码中,对于包含条件WHERE的SQL,使用SQLC命名参数。

huangapple go评论89阅读模式
英文:

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结构体中使用namedescription而不是Column1Column2

英文:

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.

huangapple
  • 本文由 发表于 2021年8月5日 21:58:12
  • 转载请务必保留本文链接:https://go.coder-hub.com/68668015.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定