You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Hello everyone,
I'm facing an issue while implementing a dynamic ORDER BY without screwing up the query. I'm almost sure it's just a lack of knowledge of the proper method, and yet I cannot figure it out or find it.
The background is that I'm working on an API that allows to specify both sorting parameters and direction, so my first implementation of the SQL fragment for the sorting was (similar) to this:
constf=sql.fragment`... the rest of the query${sortKeys.length>0 ? sql.fragment` ORDER BY ${sql.join(sortKeys.map(({ key, direction })=>sql.fragment`"${key}" ${direction}`,),sql.fragment`, `,)}` : sql.fragment``}`
An important note is that the key parameter needs to be wrapped with "" quotes since the columns in the query are (and need to be) case sensitive.
This creates a prepared statement that is more or less like ORDER BY $1 $2, and it turns out that prepared statements cannot have variables in the ORDER BY section, and so the SQL is not valid.
After that I tried with sql.literalValue, even if its usage is discouraged. However, the casting of the string makes the final query look like `"'something'", which wasn't correct.
I ended up using a quirky detail of the backtick string implementation, which is that they are seen as array from the function they've been called on, and the first string will undergo no interpolation:
constf=sql.fragment`... the rest of the query${sortKeys.length>0 ? sql.fragment([// <-- notice the array passed to `sql.fragment`. This string will be interpolated by Javascript`ORDER BY ${sortKeys.map(({ key, direction })=>`"${key}" ${direction}`).join(', '),}`]) : sql.fragment``}`
But this doesn't feel right to me.
Is there any suggestion you can give me to achieve my goal in a more orthodox way? Can I add my ORDER BY parameters without producing an invalid SQL or avoiding the cast that comes with sql.literalValue?
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
Hello everyone,
I'm facing an issue while implementing a dynamic
ORDER BY
without screwing up the query. I'm almost sure it's just a lack of knowledge of the proper method, and yet I cannot figure it out or find it.The background is that I'm working on an API that allows to specify both sorting parameters and direction, so my first implementation of the SQL fragment for the sorting was (similar) to this:
An important note is that the
key
parameter needs to be wrapped with""
quotes since the columns in the query are (and need to be) case sensitive.This creates a prepared statement that is more or less like
ORDER BY $1 $2
, and it turns out that prepared statements cannot have variables in theORDER BY
section, and so the SQL is not valid.After that I tried with
sql.literalValue
, even if its usage is discouraged. However, the casting of the string makes the final query look like `"'something'", which wasn't correct.I ended up using a quirky detail of the backtick string implementation, which is that they are seen as array from the function they've been called on, and the first string will undergo no interpolation:
But this doesn't feel right to me.
Is there any suggestion you can give me to achieve my goal in a more orthodox way? Can I add my
ORDER BY
parameters without producing an invalid SQL or avoiding the cast that comes withsql.literalValue
?Beta Was this translation helpful? Give feedback.
All reactions