Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 413
daveashworth Avatar asked May 25 '16 20:05

daveashworth


2 Answers

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.

like image 116
daveashworth Avatar answered Oct 23 '22 21:10

daveashworth


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.

like image 33
fuzzyTew Avatar answered Oct 23 '22 23:10

fuzzyTew