CODEWITHSUNDEEP
rethinkdb
jpillora
jpillora

Reputation: 5262

Performing a nested filter/join

I have 3 tables. forums, which have many posts which have many comments. I'd like to retrieve all comments from one particular forum. I know I can perform the following as separate queries:

var chosenForumID = "...";
var posts = /* get all posts where post.forumID === chosenForumID */;
var comments = /* get all comments where comment.postID is in 'posts' */;

though is it possible with one nested query?

Upvotes: 2

Views: 499

Answers (1)

Jorge Silva
Jorge Silva

Reputation: 4614

Naive Way

You can actually do that in just one query by doing the following:

r.table('forums')
 .get(ID)
 .merge({
   'comments': r.table('posts').filter({ 'forumID': ID })('id').coerceTo('array')
      .do(function (postsIdsArray) {
        return r.table('comments').filter(function (row) {
           return postsIdsArray.contains(row('id'));
        })
      }).coerceTo('array')
 })('comments')

Better Way

If you're executing this operation a lot and you have the relationship on the child (child has a parent property pointing to the ID), then you should create a secondary index for forumID and postID. 

// Create Indexes
r.table('posts').indexCreate('forumId')
r.table('comments').indexCreate('postId')

After creating them (you only have to create them once), you can then use the getAll term to make it an index operation.

r.table('forums')
 .get(1)
 .merge({
   'comments': r.table('posts').getAll(1, { index: 'forumId' })('id').coerceTo('array')
      .do(function (postsIdsArray) {
        return r.table('comments').getAll(r.args(postsIdsArray), { index: 'postId' }).coerceTo('array')
      })
 })('comments')

Secondary indexes make this operation faster.

Upvotes: 2

Related Questions