knex.raw.query() gives error with ORDER BY binding

Question

I am adding order by binding to a knex raw query in the following way.

-- Here userIds = [1,2,3] and dataOrder='DESC'

knex.raw.query("
 select from users
 where userId in ?
 order by created ?
",
[
 userIds,
 dataOrder
])

I get this error saying

check the manual that corresponds to your MySQL server version for the right syntax 
to use near ''DESC'' at line 1

I tried dataOrder with both single quotes and double quotes.

Answer 1

When providing SQL keywords as binding values with .raw(), you need to wrap the value with another call to knex.raw(). We also need to deal with some more complexity because there is no agreement on array bindings between database providers. That leaves us with something like this:

const dataOrder = "DESC";
const userIds = [1, 2, 3];
const userIdBindings = userIds.map(_ => "?").join(",");

knex.raw(`
  SELECT * FROM users
    WHERE id IN (${userIdBindings})
    ORDER BY ??
    ?
  `,
  [...userIds, "created", knex.raw(dataOrder)]
)

Note the double ?? because this denotes an identifier binding.

You might like to consider avoiding .raw() where possible, since as you have discovered it can be error-prone. Consider:

knex('users')
  .whereIn('id', userIds)
  .orderBy('created', dataOrder)

Much simpler! If your actual query is more complex, feel free to post it: most things are possible with the query builder syntax.