how to make an advanced query

[whistlesun]

if the sql is dynamic , how to use sqlc ?
for example:

/* name: ListAuthors :many */
SELECT * FROM authors where id = ? and name = ? and age = ? and school = ? and article_count = ?
ORDER BY name;

There is a multi query , if id and age is not set , the where condition can be omitted ，like this
SELECT * FROM authors where age = ? and school = ? and article_count = ?

[aitva]

Hi @whistlesun,

You can build advanced queries by using boolean operator in your WHERE clause:

-- name: ListAuthors :many
SELECT *
FROM authors
WHERE (@name::text = '' OR name = @name)
    AND (@birth::date = '0001-01-01' OR birth = @birth)
    AND (@bio::text = '' OR bio ILIKE '%' || @bio || '%');

The conditions are evaluated as follow:

• (@name::text = '' OR name = @name) returns true if @name is an empty string or evaluates to name = @name
• AND (@birth::date = '0001-01-01' OR birth = @birth) is evaluated regardless of the result of the first condition, it returns true if @birth is the zero value of time.Time or evalues to birth = @birth
• AND (@bio::text = '' OR bio ILIKE '%' || @bio || '%') is evaluated regardless of the result of the second condition, it returns true if @birth if an empty string or evaluates to bio ILIKE '%' || @bio || '%'

You can add as many conditions as you want by following this pattern and you can try it in the playground.

[mrsufgi]

This only works for non-nullable parameters, or for values where the zero-value is enough. how about the case for boolean?

SELECT * FROM users WHERE (@active::boolean IS NULL OR active = @active)

(optional filtering by active parameter)

you can't use the zero-value of boolean (false).

ofc this can also be true for strings. where string can be NULL, empty string or values string.
Even for queries with LIMIT = $1
you can workaround it using some super high number, but that is just a hack.
you should be able to pass NULL so it falls back to LIMIT ALL.

[aitva]

Yes, it is problematic if you need to use the zero value, there is a discussion on how to fix this #451. Meanwhile, you can use a second boolean to indicate the status of your filter:

-- name: ListUsers :many
SELECT * FROM users
WHERE (NOT @is_active::boolean OR active = @active)
    (NOT @is_name::boolean OR name = @name);

Calling the generated code from Go:

type ListUsersFilter struct {
	Name *string
	Active *bool
}

func ListUsers(f ListUsersFilter) ([]*User, error) {
	var active bool
	if f.Active != nil {
		active = *f.Active
	}
	var name string
	if f.Name != nil {
		name = *f.Name
	}
	users, err := db.ListUsers(context.TODO(), db.ListUsersParams{
		IsActive: f.Active != nil,
		Active: active,
		IsName: f.Name != nil,
		Name: name,
	})
	// ...
}

[aitva]

For queries with a LIMIT clause you can use NULLIF:

SELECT *
FROM users
LIMIT NULLIF(@query_limit::int, 0);

[mrsufgi]

@aitva seems like a viable workaround, I'm pretty sure there is an issue with NULLIF flawlessly but looks OK as a bandaid.

[whistlesun]

tks

[flymedllva]

@aitva And how to live with MySQL?

[jalen-qian]

There is the same problem on mysql.