-
Notifications
You must be signed in to change notification settings - Fork 923
-
Hey, I've been using SQLC for a week or so now, and I'm really enjoying it. I have ran into a little problem, and I wanted to see if anyone had a solution!
I am trying to write a query that allows me to fetch multiple results based on a slice of inputs.
e.g.
SELECT * FROM "workspace" WHERE id IN ('1', '2', '3', ..., 'n');
I used the following query while generating my code using SQLC:
-- name: RetrieveWorkspaces :many -- RetrieveWorkspaces fetches a list of Workspaces by their ID from the database SELECT * FROM "workspace" WHERE id IN (sqlc.arg(ids)::UUID []);
The generated code looks like this:
const retrieveWorkspaces = `-- name: RetrieveWorkspaces :many SELECT id, name, created_at, updated_at FROM "workspace" WHERE id IN (1ドル::UUID []) ` // RetrieveWorkspaces fetches a list of Workspaces by their ID from the database func (q *Queries) RetrieveWorkspaces(ctx context.Context, ids []uuid.UUID) ([]Workspace, error) { rows, err := q.db.QueryContext(ctx, retrieveWorkspaces, pq.Array(ids)) if err != nil { return nil, err } defer rows.Close() var items []Workspace for rows.Next() { var i Workspace if err := rows.Scan( &i.ID, &i.Name, &i.CreatedAt, &i.UpdatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Close(); err != nil { return nil, err } if err := rows.Err(); err != nil { return nil, err } return items, nil }
But my tests fail with the following message from Postgres:
--- FAIL: TestRetrieveWorkspaces (0.02s)
/pkg/database/sqlc/workspace_test.go:90:
Error Trace: workspace_test.go:90
Error: Received unexpected error:
pq: operator does not exist: uuid = uuid[]
Test: TestRetrieveWorkspaces
Am I missing something here? I also tried it without the sqlc.arg(ids)::UUID [] part, and just used 1ドル::UUID [] but that resulted in the parameter being named dollar_1.
Beta Was this translation helpful? Give feedback.
All reactions
I have figured it out! I hadn't read this properly, and thought it was a different kind of query! I still had to rename the value using sqlc.arg(ids), as it was still being named dollar_1, but that's fine 😊
For reference, the following statement worked:
-- name: RetrieveWorkspaces :many -- RetrieveWorkspaces fetches a list of Workspaces by their ID from the database SELECT * FROM "workspace" WHERE id = ANY(sqlc.arg(ids)::uuid []);
Replies: 1 comment
-
I have figured it out! I hadn't read this properly, and thought it was a different kind of query! I still had to rename the value using sqlc.arg(ids), as it was still being named dollar_1, but that's fine 😊
For reference, the following statement worked:
-- name: RetrieveWorkspaces :many -- RetrieveWorkspaces fetches a list of Workspaces by their ID from the database SELECT * FROM "workspace" WHERE id = ANY(sqlc.arg(ids)::uuid []);
Beta Was this translation helpful? Give feedback.