0

i have this query:

 r.db('test').table('users').getAll("amazon_11",{index:"parent"})
    .innerJoin(r.table("posts"),function (posts, user) {return posts("employeeId").eq(user("employeeId"));}).zip()
    .innerJoin(r.table("posts_facebook"),function(left,right){return left('id').eq(right('post_id'))}).zip()

i want to add a condition on the timestamp field in the collection posts_facebook.

I have created an index on the timestamp field.

This is what my guess was :

  r.db('test').table('users').getAll("amazon_11",{index:"parent"})
    .innerJoin(r.table("posts"),function (posts, user) {return posts("employeeId").eq(user("employeeId"));}).zip()
    .innerJoin(r.table("posts_facebook"),function(left,right){return left('id').eq(right('post_id'))}).zip()
  .between(fromDate,toDate,{index:"approvedAt"})

Error received from rethinkdb is as follows:

e: Expected type TABLE_SLICE but found SEQUENCE: VALUE SEQUENCE in:
r.db("test").table("users").getAll("amazon_11", {"index": "parent"}).innerJoin(r.table("posts"), function(var_43, var_44) { return var_43("employeeId").eq(var_44("employeeId")); }).zip().innerJoin(r.table("posts_facebook"), function(var_45, var_46) { return var_45("id").eq(var_46("post_id")); }).zip().between("2016-08-01 11:31:40", "2016-08-01 11:32:00", {"index": "approvedAt"})
The date format is : YYYY-MM-DD h:i:s

user284873
  • 3
  • 4
  • Hello and welcome to [SO].Please take time out for a [tour] and visit the [help]. You might also want to read [ask]. – Sampada Aug 02 '16 at 10:44

1 Answers1

0

If you're already using innerJoin, I would just put that in the body of the second innerJoin like .innerJoin(r.table("posts_facebook"),function(left,right){return left('id').eq(right('post_id')).and(right('approvedAt').gt(fromDate)).and(right('approvedAt').lt(toDate));}). Note that innerJoin will be very slow on large tables, so you may want to look into using concatMap instead.

mlucy
  • 5,249
  • 1
  • 17
  • 21
  • how do i put an index based clause in the subquery rather than the gt and lt functions, i have already created an index on the date field (approvedAt). – user284873 Aug 05 '16 at 08:05
  • You can't. If you could, it wouldn't make the query any faster: the `innerJoin` already has to look at every document, so the index can't accelerate it. If you rewrote the query to use `concatMap` and `between` inside the `concatMap`s, you could use the index in that `between`. – mlucy Aug 05 '16 at 20:55