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!
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With