Building RethinkDB filter dynamically

Question

I want to filter my data using filter specified as below:

var filter = { minAge: 2, maxAge: 11, country:'uk', city':'london'};

In which case the filtering will be:

r.db(dbName).table(tableName)
    .filter(r.row("minAge").ge(filter.minAge)
             .and(r.row("maxAge").le(filter.maxAge))
             .and(r.row('country').eq(filter.country))
             .and(r.row('city').eq(filter.city))
           );

However some of the filter predicates could be missing for example if I have only min age and city, I want to filter only on them:

var filter2 = { minAge: 2, city':'london'};

Above filter should result in below

r.db(dbName).table(tableName)
    .filter(r.row("minAge").ge(filter.minAge)             
             .and(r.row('city').eq(filter.city))
           );

How to build a ReQL query according to my filter object keys that I could pass to the filter function.

kureikain · Accepted Answer · 2015-09-12T19:48:50.090

2

Sorry, I understand wrong and re-write my answer.

I think what you can do is to write a generic function that run on client to return query for filter.

First of all, if you are dealing with filter object dynamically, the filter function has no knowledge of which operation to apply. Given this:

{city: 'London', minAge: 12}

For city we want a eq, for minAge we want a ge but since we are doing this dynamically, it's prolly better to propose a syntax for filter object like this:

{city: 'London', minAge: ['ge', 12]}

With that in mind, I propose this solution:

var filterObject = {
  minAge: ['ge', 12],
  city: 'london'
}

r.table('monitor').filter((function(filterObject) {
  var condition

  for (var key in filterObject) {
    var conditionForThisKey
    if (typeof key == 'string' || typeof key == 'number') {
      conditionForThisKey = r.row(key).eq(filterObject[key])
    } else {
      conditionForThisKey = r.row(key)[filterObject[key][0]](filterObject[key][1])
    }

    if (typeof condition === 'undefined') {
      condition = conditionForThisKey
    } else {
      condition = condition.and(conditionForThisKey)
    }
  }

  return condition
})(filterObject))
.run(connection)
.then(function(cursor){ //do stuff with result })
.error(function(err) { //err handling })

This way, We build condition object for RethinkDB on client dynamically. I'm thinking that we can push that logic to RethinkDB with r.js. But I don't see a point to let that run on RethinkDB, client has capable of that.

Let me know if this helps.

edited Sep 12 '15 at 19:48

answered Sep 11 '15 at 20:03

kureikain

2,304
2
14
9

Thank you for your reply, but data is always present in the table row, its the filter that might be missing certain predicate, which means that user doesn't care about that field, say city for example. – slobodan.blazeski Sep 12 '15 at 09:21
Yes, I understand. That's why we have branch. We return `true` in branch to say that, if the field doesn't exist, then don't compare it, just return true. So given a document without `city` field, those are equivalent: r.row('minAge').ge(filter.minAge) r.row('minAge').ge(filter.minAge) .and(r.branch(r.row.hasField('city'), r.row('city').eq(filter.city)) By using `branch`, you move the logic of checking condition to RethinkDB. If the field exist, do the compare logic, if the field doesn't exist, don't do anything and just return true. – kureikain Sep 12 '15 at 16:09
Forgive me if my question isn't clear or if I don't understand well your answer ,I started to read through simply rethink just yesterday, great book so far. However it seems to me that branch predicate r.row.hasFields checks does the table row has some field, as I said it always does, the field that might be missing is in the filter object.so if I use branch it should be something like r.branch(filter.hasOwnProperty('city'),r.row('city').eq(filter.city),r.expr(true)) and even if that is possible I don't think its wise to check everytime does filter has property. – slobodan.blazeski Sep 12 '15 at 17:37
My bad. I tottally understand wrong your question. I thought the field was missing in document, instead of missing from filter object as in your question. Somehow I misunderstand that. Let me rethink it a bit and see what we came up with. – kureikain Sep 12 '15 at 18:57
Hey @slobodan.blazeski I came up with a solution. I hope it works for you. Let me know. I apologize for understand wrong question. Also, thanks for liking Simply RethinkDB. It still has many grammar, syntax to fix. Stay tune for more – kureikain Sep 12 '15 at 19:54

score 0 · Answer 2 · answered Jun 08 '17 at 13:53

A moderately dirty trick to avoid filtering by missing fields is to just add default values -

r.db(dbName).table(tableName)
   .filter(r.row("minAge").default(filter.minAge).ge(filter.minAge)
      .and(r.row("maxAge").default(filter.maxAge).le(filter.maxAge))
      .and(r.row('country').default(filter.country).eq(filter.country))
      .and(r.row('city').default(filter.city).eq(filter.city))
   );

It's not efficient, it's not great, but it should work if you don't want to over-engineer it for the time being.

Please note that sequential scans - queries without using a limit early in the query, or queries without any indexes, with just .filter() function on the whole table - will use a sequential indexes, and are SLOW. It doesn't scale well.

score 0 · Answer 3 · answered Feb 16 '22 at 19:29

If anyone's looking for a solution, here's one from a maintainer (srh).

RethinkDB queries are just objects and you can make general functions to build them for you.

    function makeFilterExpr(x, obj) {
      let expr = r.expr(true);
      if ('minAge' in obj) { expr = expr.and(x('age').ge(obj['minAge'])); }
      if ('city' in obj) { expr = expr.and(x('city').eq(obj['city'])); }
      return expr;
    }
    
    // usage:
    let query = r.table('foo').filter(x => makeFilterExpr(x, {'minAge': 2, 'city': 'London'}));

You just have to write the code that makes the query.

Building RethinkDB filter dynamically

3 Answers3