With knexjs, how do I compare two columns in the .where() function?

Question

Using knexjs only (no bookshelf) I would like to do something like the following query:

select * from table1 where column1 < column2

However, when I do this:

.table("table1").select().where("column1", "<", "column2")

The SQL that knexjs generates is:

select * from table1 where column1 < 'column2'

Which doesn't give the desired result b/c it's not comparing the value from the column, it's comparing the value of the string, 'column2'.

Anyone know how to do what I'm wanting? Thanks!

score 22 · Accepted Answer · answered May 25 '16 at 21:20

22

Ok, so after some digging, it looks like it can be done this way. Not sure if this is best practice, but at the moment, it works so until I hear otherwise...

.table("table1").select().where("column1", "<", knex.raw("table1.column2"))

Again, not ideal, but it gets the job done. Just be sure to

import knex from "knex";

at the top of whatever file you're using this in.

answered May 25 '16 at 21:20

daveashworth

580
5
15

8

.whereRaw("?? = ??", ["column1", "column2"]) works too – Greg Hornby Mar 21 '17 at 01:47
@GregHornby it works. Could you please explain why it works only with double question marks? – jean d'arme Feb 18 '19 at 16:02
2

`??` represents a binding for an identifier like a column or table name. `?` represents a binding for a value, like a number, or string. – Greg Hornby Feb 18 '19 at 22:14

score 12 · Answer 2 · answered Jun 06 '19 at 23:05

As of knex 0.15.0 (July 2018), we now have:

table("table1").select().where("column1", "<", knex.ref("column2"))

See https://knexjs.org/#Ref

As Greg Hornby mentions in the comment to the other answer, you can also use ?? in a raw query to bind to a column or table name.

With knexjs, how do I compare two columns in the .where() function?

2 Answers2

Linked