CODEWITHSUNDEEP
mysqlnode.jsknex.js
starleaf1
starleaf1

Reputation: 2872

How to execute a MySQL function in a Knex query?

I have a BINARY field in my table which I usually grab like this:

SELECT HEX(users.id) AS id FROM users WHERE username = ?

I recently started using Knex because I need to be able to dynamically generate WHERE clauses from objects. Here's what I tried:

knex('users').select('HEX(users.id) AS id)').where(filter);

Here's the query it generates:

select `HEX(users`.`id)` as `id` ....

And then I tried this:

knex('users').select('HEX(`users`.`id`) AS id').where(filter);

And it comes up with this:

select `HEX(``users```.```id``)` as `id` ....

How do I execute HEX() without it being mistaken for a column name?

Upvotes: 8

Views: 8198

Answers (2)

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

With knex letting to do quoting of identifiers it would look like this:

knex('users').select(knex.raw('HEX(??) AS id', ['users.id'])).where(filter);

Upvotes: 7

starleaf1
starleaf1

Reputation: 2872

I've found a solution. I have to use raw() function. So my query builder will look like this:

knex('users').select(knex.raw('HEX(`users`.`id`) AS id')).where(filter);

Upvotes: 3

Related Questions