Support dynamic sized IN clauses

[oralordos]

You should add support for a query with a dynamically sized list of values for an IN clause in a WHERE segment. This would be useful for when you are using a dataloader to get data from the database. This is most useful for graphql servers.

See the large example on this page for a use-case: https://gqlgen.com/reference/dataloaders/

I am thinking something like:

-- name: GetPeopleMany :many
SELECT * FROM people
WHERE id IN ($);

would generate a go function that takes in a slice of values for the input. This is distinguished from just wanting a single value by only having the $ instead of $1.

[kyleconroy]

You're in luck! sqlc already supports this using ANY. The following SQL:

-- name: GetPeopleMany :many
SELECT * FROM people
WHERE id = ANY($1::int[]);

generate this method signature

func (q *Queries) GetPeopleMany(ctx context.Context, ids []int) ([]People, error) {
  ...
}

[markschmid]

Any way for this to work with mysql as well? Tried ANY(?::int[]) but it reports a syntax error.

[SebastienMelki]

Any way for this to work with mysql as well? Tried ANY(?::int[]) but it reports a syntax error.

@kyleconroy I have the same issue, should this work for mysql or is it only available in postgres?

[josharian]

Ditto, but sqlite.

[orisano]

please use sqlc.slice()
https://play.sqlc.dev/p/ed1b1d235f3a32c811a27dec68d826b4c0c36dedf0e614f0339fa02e11cc4ba4

[iceteahh]

-- name: GetPeopleMany :many
SELECT * FROM people
WHERE id = ANY($1::int[]);

This won't work with enum type

[mbesida]

-- name: GetPeopleMany :many SELECT * FROM people WHERE id = ANY($1::int[]);

This won't work with enum type

At the moment(Apr 2024) it works with enum types. You just need to specify the enum type (e.g. ANY($1::MyEnum[]))

[psnehanshu]

@mbesida This cause error:

failed to encode args[n]: unable to encode []CustomEnumType{\"v1\"} into text format for unknown type (OID 1234): cannot find encode plan

[BijaySharma]

I used it this way and it works like charm.

-- name: GetPeopleMany :many
SELECT * FROM people
WHERE id IN (sqlc.slice('ids'));