Incorrect value index when using where(sql(...))
w33ble opened this issue · 0 comments
w33ble commented
I ran in to an issue when using multiple .where(...)
methods with nested sql(...)
methods. Here's a pretty simple example:
const query = sql.select('*')
.from('user')
.join('comments').on({ 'comment.user_id': 'user.id' })
.where({ 'user.id': 1 })
.where(sql('comment.published = $', true))
.where({ 'user.active': true })
.orderBy('comment.created_at')
Using this with .toString()
works fine, but as the docs point out, this string shouldn't be used to execute queries as the values are not properly escaped. When using .toParams()
, the result is close, but the indexes in the statement are incorrectly numbered. Here's the output:
{
text: 'SELECT * FROM "user" INNER JOIN comments ON comment.user_id = "user".id WHERE "user".id = $1 AND comment.published = $2 AND "user".active = $4 ORDER BY comment.created_at',
values: [ 1, true, true ]
}
Note that the indexes are $1
, $2
, and $4
, and there is no $3
. As a result, when passing the text and values into the pg client, you get an error. It only seems to happen if you call .where(...)
after you call .where(sql(...))
, and it keeps getting worse if you have multiple .where(sql(...))
calls.