whereRaw not working properly (adonis-lucid v6.1.3)

There seems to be a problem with whereRaw(). i have looked at the source (knex) where my code throws the error on /src/raw.js on line 138. I have also looked at example of how whereRaw() is used as well as the docs.

I am using a ServiceRides Lucid model (and also UUIDs - v1)

let query = ServiceRides.query();

await query.select(query.db.knex.raw('YEAR(created_at) as year_of_trip'))
.whereRaw('tenant_id = ? AND completed = ? AND YEAR(created_at) >= ? AND YEAR(created_at) <= ?', ["30fb73d0-9e3d-11e9-bd80-3741ce1df8fb", 1, 2018, 2019])
.groupByRaw('YEAR(created_at)')
.orderByRaw('YEAR(created_at) DESC')

The above code throws Error Expected 1 bindings, saw 0

Someone ( @elihigu ) of the knex core team could not reproduce the error here for knex v0.18.2 however this error surfaces in knex v0.16.3 (used in adonis-lucid 6.1.3).

Please, what to do ? Help me please… (NB: The only problem here is the whereRaw() part)

1 Like

I can understand if this is not the answer you may be looking for. But, here it is…

Can you convert the entire query to Database.raw()? Doing this way is indeed more cumbersome to build queries, but I came out unscathed out of a similar requirement when whereRaw couldn’t help.

1 Like

I fixed it! Took some perseverance however (had to dig into the knex codebase on GitHub to do this… Lol). Turns out that there are two parameter binding methods you can use with whereRaw() namely:

  1. Array-index based parameter binding (this doesn’t work - as shown in my issue)
  2. Object-key based parameter binding (this works!)

Here is the code of how i did it so it can help someone else

let query = ServiceRides.query();

query.select(query.db.knex.raw('YEAR(created_at) as year_of_trip'))
.whereRaw('tenant_id = :tenant_id AND completed = :completed AND YEAR(created_at) >= :year_registered AND YEAR(created_at) <= :year_current', {tenant_id:"30fb73d0-9e3d-11e9-bd80-3741ce1df8fb", completed:"1", year_registered:"2018", year_current:"2019"})
.groupByRaw('YEAR(created_at)')
.orderByRaw('YEAR(created_at) DESC')

Thanks a lot @prashanth1k for your input, i appreciate

1 Like

That is cool…!

Or.
.whereRaw('tenant_id = ${tenant_id} AND completed = 'Completed' AND ...
and so forth.

That was useful to know, thank you.

The named parameter replacement is WAY nicer than a string with n?” placeholders in it too… nice find!

@prashanth1k I would probably avoid

.whereRaw(`something = ${some_var}`)

Unless you’re absolutely certain that “some_var” is a safe value, the purpose of doing token replacement instead of directly injecting values into a query string is so that things get properly escaped/etc to prevent sql injection and so forth. Directly injecting variables into query strings is usually unwise. :slight_smile:

2 Likes