Filter including field in formula

Question

I want to filter a collection including collection's fields in the criteria.

- myCollection
|- name (string)
|- field1 (int)
|- field2 (int)

I want to get all names where field1 > field2. I thought it could work this way:

db.collection('myCollection').where('field1','>','field2')
  .get().then(function(snapshot){
    //doing something
  });

However, it was understood as field1 > 'field2', instead field1 > field2.

I would like to go further, doing something like field1+field2<1000, but both of following queries got no result

db.collection('myCollection').where('field1'+'field2','<',1000)
  .get().then(function(snapshot){
    //doing something
  });

db.collection('myCollection').where('field1+field2','<',1000)
  .get().then(function(snapshot){
    //doing something
  });

Thanks

Answer 1

There is no way to specify field references in the value of an expression.

The best approach I can think of is to store the value of field2 - field1 in a separate field, so that you can compare that against a constant. E.g. if you store the delta of field2 and field 1 in delta_field2_field2, you'd get this query:

db.collection('myCollection').where('delta_field2_field2','<', 0)

Then if you store the sum of field 1 and 2 in sum_field1_field2 you'd get:

db.collection('myCollection').where('sum_field1_field2','<',1000)

You'll see this is a common theme across NoSQL databases: if an operator you want isn't built-in, you can often add data to your model to allow for the use-case.