SQLC named parameter in generated code for sql containing conditional where

Question

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

Not related to your question but, what do you expect `'%$1%'` and `'%$2%'` to resolve to? Will `sqlc` fix that for you? Or are those the actual string literals against which you would like to do the search? — mkopriva, Aug 05 '21 at 14:05
Another thing, `$1::varchar(100) IS NULL` and `$2::varchar(100) IS NULL` will never evaluate to true with the code that `sqlc` generated. This is because value of `string` type (`Column1` and `Column2`) passed in to `QueryContext` will never result in SQL's `NULL`. — mkopriva, Aug 05 '21 at 14:14
As far as named parameters are concerned, perhaps what you looking for is described here: https://docs.sqlc.dev/en/latest/howto/named_parameters.html. — mkopriva, Aug 05 '21 at 14:19
@mkopriva How will you use conditional where with like operator? — Anup, Aug 05 '21 at 14:59
Use [string concatenation](https://postgresql.org/docs/13/interactive/functions-string.html) inside SQL, e.g. `name LIKE '%' || $1 || '%'`. — mkopriva, Aug 05 '21 at 15:00
@mkopriva How will you define conditional where. If $1::varchar(100) IS NULL does not return true shall I use $1 IS NULL instead? — Anup, Aug 05 '21 at 15:10
That won't help because `$1` itself will never be `NULL` if the passed in value's type is `string`, and casting it to whatever type will not make it `NULL`. You could simply check against an empty string, e.g. `$1 = '' OR name LIKE '%' || $1 || '%'`. — mkopriva, Aug 05 '21 at 15:16
In Go the only way for a parameter to result in `NULL` is if the argument's type is one of those types that can be `nil`. For example if you had `Column1 *string` then `Column1` could be `nil` and therefore `$1` could potentially result in `NULL` and then it would make sense to have a conditional like `$1 IS NULL`. But if `$1` cannot be `NULL` in the first place then that conditional is simply unnecessary, you could just as well write `false OR name LIKE '%' || $1 || '%'`. — mkopriva, Aug 05 '21 at 15:22
Forgot one thing, the other way an argument can result in `NULL` is if the Go type implements the `driver.Valuer` interface. You can see such a type [here](https://pkg.go.dev/database/sql#NullString), it itself cannot be `nil` but its value can result in `NULL`. — mkopriva, Aug 05 '21 at 15:24

Pablo Olivera · Answer 1 · 2022-04-13T03:07:56.737

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.

SQLC named parameter in generated code for sql containing conditional where

1 Answers1