Using knexjs, how do I compare two columns in a .where () function?

Question

Using only knacks (without a 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") 

SQL that knexjs generates:

 select * from table1 where column1 < 'column2' 

Which does not give the desired b / c result, it does not compare the value from the column, comparing the value of the string "column2".

Does anyone know how to do what I want? Thanks!

Answer 1

Okay, so after some digging, it seems like you can do it like this. Not sure if this is the best practice, but at the moment it works this way until I hear otherwise ...

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

Again, not perfect, but he is doing his job. Just make sure

 import knex from "knex"; 

at the top of any file you use this in.

Answer 2

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 a comment on another answer, can you also use ?? in a raw query to bind to a column or table name.