Compare two fields in Waterline/Sails.js query

Question

I want to compare two fields in my Waterline query in Sails.js application, e.g.: SELECT * FROM entity E WHERE E.current < E.max.

I've tried the following code, but it's expecting integer value to be passed to it instead of column name:

Entity.find({
  where: {
    current: {'<': 'max'}
  }
});

So, how do I compare two columns?

Answer 1

The other way would be to use one query to get the max before putting it in the criteria.

EntityOne.find({
  sort: 'column DESC'
}).limit(1)
  .exec(function(err,found){
    var max = found[0]
    EntityTwo.find({
      where: {
        current: {'<': found}
      }
    }).exec((err,found) {
      // Do stuff here
      });
  });

The query method is ultimately going to be faster however

Answer 2

I have ran some tests and at the same time read the Waterline documentation. There is no indication of anything that could possibly do comparison of two fields/columns via .find() or .where() methods. Reference: http://sailsjs.org/documentation/concepts/models-and-orm/query-language

Instead, I have used .query() method to compare two fields via SQL string such as :

Entity.query("SELECT * FROM `Entity` E WHERE E.current < E.max", function( err, res) {
  if(err) {
    //exception
  } else {
    console.log('response',res);
  }
});