It’s tricky because we’re not used to write real SQL when using query builders
Speak for yourself. I quite frequently write ad-hoc queries (yes writing the queries, not asking a fucking robot in 8 iterations of increasingly verbose detail to write it for me). That works perfectly well because I know what I want at the time I’m writing the query.
That isn’t at all the same thing as using a query builder to allow the application to be able to filter on arbitrary columns, or complex value types, or have conditional conditions, or conditional columns returned, or any number of other dynamic aspects that are literally the reason for query builders to exist.
as soon as you learn how to do what you did with a builder with pure SQL, well it becomes trivial.
Ok fuck it, I’ll bite. What’s the “trivial, pure SQL” to take a variable length array/list/set/tuple/<whatever structure your language uses for a bunch of values>, and check if a column is equal to one of them.
i.e convert ['foo', 'bar', 'baz'] into ...WHERE col IN ('foo', 'bar', 'baz');.
I guess that’s great if your DB supports an array type, and supports it for ANY(), and the language bindings/driver have support for binding arrays natively.
Speak for yourself. I quite frequently write ad-hoc queries (yes writing the queries, not asking a fucking robot in 8 iterations of increasingly verbose detail to write it for me). That works perfectly well because I know what I want at the time I’m writing the query.
That isn’t at all the same thing as using a query builder to allow the application to be able to filter on arbitrary columns, or complex value types, or have conditional conditions, or conditional columns returned, or any number of other dynamic aspects that are literally the reason for query builders to exist.
Ok fuck it, I’ll bite. What’s the “trivial, pure SQL” to take a variable length array/list/set/tuple/<whatever structure your language uses for a bunch of values>, and check if a column is equal to one of them.
i.e convert
['foo', 'bar', 'baz']into... WHERE col IN ('foo', 'bar', 'baz');.score 1 · canonical
I do it with
col = ANY($1), and $1is bound as a single valueARRAY[]on the app side (in Go I usepq.Arrayfromlib/pqwhich does this)ANYis easier to handle thanINin this kind of queriesscore 1 · canonical
I guess that’s great if your DB supports an array type, and supports it for ANY(), and the language bindings/driver have support for binding arrays natively.
score 1 · canonical