How to apply LOWER() to a ref in Objection

user_6546440 0
Viewed 291

Im using a like where statement in my query like this:

import { ref } from 'objection'

// [...]

query = query.where(ref('data.nested_data').castText(), 'like', `%${value}%`)

And its working fine. The resulting query looks like this:

WHERE CAST("data"#>>'{nested_data}' AS text) like '%example%'

Now I want to use LOWER() funcion in the key, so I can find results case-insensitive. So my query now looks like this:

import { ref, raw } from 'objection'

// [...]

query = query.where(raw(`LOWER(${ref('data.nested_data').castText()})`), 'like', `%${value.toLowerCase()}%`);

But this results to the following SQL query (throwing an error):

WHERE LOWER([object Object]) like $1

-- ERROR:  syntax error at or near "["
-- LINE 6: WHERE LOWER([object Object]) like $1

Is there any way to achieve what Im looking for? I prefer not using a raw query since my type to cast is dynamic

objection.js knex.js node.js postgresql javascript
1 Answers

The people maintaining Objection helped me solving this issue at github.

The correct syntax is:

const key = ref('data.nested_data').castText();
query = query.where(raw('LOWER(??)', [key]), 'like', `%${value.toLowerCase()}%`);
user_65464400
Related