Support passing slices to MySQL queries

[kyleconroy]

While MySQL supports the ANY operator, it does not support arrays. This means the currently documented way to pass slices to a query will not work.

[kyleconroy]

Here's some related discussion from the go-sql-driver repository:

• in (?) cannot work and no error  go-sql-driver/mysql#107
• Using ? with slices go-sql-driver/mysql#176

[kyleconroy]

The MEMBER OF syntax may offer a solution. The problem is that you need to encode the slice as JSON before passing it into the query.

CREATE TABLE pkg (
  id text NOT NULL,
  vic VARCHAR(50) NOT NULL,
  kim text NOT NULL,
  order INTEGER NOT NULL,
  PRIMARY KEY (id)
);

/* name: GetPkgID :many */
SELECT id, vic, kim FROM pkg
WHERE vic MEMBER OF(?) AND shop_id = ?;

[kyleconroy]

Okay, here's my current idea. We add a second type parameter to sqlc.arg. That parameter, if passed, will compile to a cast statement. We can support the ARRAY type syntax in this type parameter and generate the correct code.

CREATE TABLE users (id SERIAL);

-- name: FilterUsers :many
SELECT * FROM users
WHERE id MEMBER OF(sqlc.arg('ids', 'SERIAL ARRAY'));

const filterUsers = `-- name: FilterUsers :many
SELECT id FROM users
WHERE id MEMBER OF(CAST(? AS JSON));
`

func (q *Queries) FilterUsers(ctx context.Context, ids []int64) ([]User, error) {
	// ...
}

A few issues I can think of:

• The array type must convert to a scalar type that can be represented inside a JSON array
• We'd need to generate a helper method that converts slices to JSON
• We'd probably also need to add a method that unmarshals JSON as well

[warent]

I'm not sure if it's bad practice, but maybe the generated code could produce a dynamic query, generating N placeholders for each array value. So, something like:

func filterUsers(vals []interface{})
{
return fmt.Sprintf(`-- name: FilterUsers :many
SELECT id FROM users
WHERE id IN (?%v);
`, strings.Repeat(",?", len(vals)-1));
}

[yeka]

Just throwing an idea (or hack) regarding IN statement in SQL.

Current behaviour:

const listAuthors = `-- name: ListAuthors :many
SELECT id, name FROM authors WHERE id IN($1) AND name = $2;
`

type ListAuthorsParams struct {
	ID   int 
	Name string
}

func (q *Queries) ListAuthors(ctx context.Context, arg ListAuthorsParams) ([]Author, error) {
	rows, err := q.db.QueryContext(ctx, listAuthors, arg.ID, arg.Name)

My idea is to change the parameter into slice and expanding the query based on the size of the slice.
What I meant by expanding is to replace id IN ($1) into id IN($1, $2, $3, ...).
If there's 3 ids, name = $2 will become name = $4.
Using the same query, the generated code will now look like this:

type ListAuthorsParams struct {
	ID   []int 
	Name string
}

func (q *Queries) ListAuthors(ctx context.Context, arg ListAuthorsParams) ([]Author, error) {
	query, args := q.expand(listAuthors, expandArg{arg.ID}, arg.Name)
	rows, err := q.db.QueryContext(ctx, query, args...)

The expandArg type is used to differentiate normal slice with slice from IN statement.
The q.expand() function will return modified query and args.

For example, given that arg = ListAuthorsParams{[]int{9, 8, 6, 7}, "Joe"}, q.expand() will return:

query = "SELECT id, name FROM authors WHERE id IN($1, $2, $3, $4) AND name = $5;"
args = []interface{}{9, 8, 6, 7, "Joe"}

The code for q.expand() can be seen here. I haven't managed to find how to change arg.ID into []int.

Not forget to mention, this solution will not work for prepared statement.

[mightyguava]

How about for MySQL only, ( ? ) will be interpreted as accepting a list of items of that type, rather than a single one?

Pros

• It's still valid SQL
• It would not be confusing to users. As in previous issues like [mysql] Not support where id in (?) #706, in (?) cannot work and no error  go-sql-driver/mysql#107, Using ? with slices go-sql-driver/mysql#176, people naturally assume you can pass in a slice. Any strongly-typed language will prevent you from passing in a single value.

Cons

• The type of the binding differs from what you'd have to pass in to the driver, but sqlc already abstracts the driver away so that's fine.
• What would you do about (?, ?, ?) etc...? Imo sqlc could just error in the parse stage and say that you have to write your query as a single (?)

[namndev]

Hi @kyleconroy,

Good your idea. But

SELECT id FROM users
WHERE id MEMBER OF(CAST(? AS JSON))

Syntax error with keyword MEMBER OF ? I use sqlc 1.6.0.

[namndev]

I have an idea,

using FIND_IN_SET

SELECT id FROM users
WHERE FIND_IN_SET(id, ?)

But name of variable after run sqlc generate: FINDINSET and FINDINSET_2, ...
@kyleconroy, How to fix or rename variable in above?

[fr3fou]

How can I achieve this in postgres?

[xiazemin]

Just throwing an idea (or hack) regarding IN statement in SQL.

Current behaviour:

const listAuthors = `-- name: ListAuthors :many
SELECT id, name FROM authors WHERE id IN($1) AND name = $2;
`

type ListAuthorsParams struct {
	ID   int 
	Name string
}

func (q *Queries) ListAuthors(ctx context.Context, arg ListAuthorsParams) ([]Author, error) {
	rows, err := q.db.QueryContext(ctx, listAuthors, arg.ID, arg.Name)

My idea is to change the parameter into slice and expanding the query based on the size of the slice.
What I meant by expanding is to replace id IN ($1) into id IN($1, $2, $3, ...).
If there's 3 ids, name = $2 will become name = $4.
Using the same query, the generated code will now look like this:

type ListAuthorsParams struct {
	ID   []int 
	Name string
}

func (q *Queries) ListAuthors(ctx context.Context, arg ListAuthorsParams) ([]Author, error) {
	query, args := q.expand(listAuthors, expandArg{arg.ID}, arg.Name)
	rows, err := q.db.QueryContext(ctx, query, args...)

The expandArg type is used to differentiate normal slice with slice from IN statement.
The q.expand() function will return modified query and args.

For example, given that arg = ListAuthorsParams{[]int{9, 8, 6, 7}, "Joe"}, q.expand() will return:

query = "SELECT id, name FROM authors WHERE id IN($1, $2, $3, $4) AND name = $5;"
args = []interface{}{9, 8, 6, 7, "Joe"}

The code for q.expand() can be seen here. I haven't managed to find how to change arg.ID into []int.

Not forget to mention, this solution will not work for prepared statement.

i fufill a version that solve this problem https://github.com/xiazemin/sqlc

[asterikx]

Is there a workaround for the time? Listing by IDs seems to be a rather crucial thing to do after all, e.g. for pre-loading associations