4 ms·
Dumb question: how do you write dynamic queries using sqlc? From the doc I see: -- name: ListAuthors :many SELECT * FROM authors ORDER BY name; Bu
by sdevonoes 5y ago
Dumb question: how do you write dynamic queries using sqlc? From the doc I see:
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
But what if I want to get a list of authors by date? Shall I do:
-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;
-- name: ListAuthorsByDate :many
...
And what if later I need to get a list of authors by date and name? Should I write yet another query? I'm used to write one generic (composable) query that is very handy in situations where one need to retrieve rows by many different filtering criteria. Is that possible in sqlc?
- sa46 5y agoDynamic queries are difficult to model when starting from a static query since SQL doesn't compose well. The workarounds I've used: - Use multiple queries that share the same output type. - Push the predicate into the query directly. However, you can't pass an expression for an order by column since the order by clause takes a name, not an expression. Postgres doesn't allow using names as arguments to a prepared query so that leaves either adding an annotation like sqlc.order_by that's dynamically added to a query string, or by getting more creative with the structure of the query: SELECT * FROM AUTHORS WHERE sqlc.arg('by_date') ORDER BY DATE UNION ALL SELECT * FROM AUTHORS WHERE NOT sqlc.arg('by_date')