Subqueries to filter out in rethinkdb

Question

How do write an equivalent statement in RethinkDB using Python client driver?

SELECT id fields FROM tasks WHERE id NOT IN (SELECT id FROM finished_tasks)

This is what I tried:

r.table('tasks').filter(lambda row: r.not(row['id'] in r.table('finished_tasks').pluck("id").coerce_to('array').run()

score 0 · Answer 1 · answered Sep 25 '15 at 14:23

In Java Script:

r.table("tasks").filter(function(task){
 return r.expr(r.table("finished_tasks").pluck("id")).map(function(i){
       return i("id");
    }).coerceTo('array')
      .contains(task("id"))
      .not();

})

In Python should be something like this.

score 0 · Answer 2 · answered Sep 25 '15 at 17:06

I don't have an example in Python. I give JavaScript example and I think you can compare on API doc to write Python equivalent.

Assume that id is also the primary key of finished_tasks table.

r.table('tasks').filter(function(task) {
  return r.table('finished_tasks').get(task('id')).eq(null)
})

If id isn't primary key of finished_tasks, let's create a secondary index for it, then use it in getAll

// Create index
r.table('finished_tasks').indexCreate('finished_task', r.row('id'))
// Using index for efficient query
r.table('tasks').filter(function(task) {
  return r.table('finished_tasks').getAll(task('id'), {index: 'finished_task'}).count().eq(0)
})

Subqueries to filter out in rethinkdb

2 Answers2