CODEWITHSUNDEEP
rethinkdb
Lu4
Lu4

Reputation: 15032

Joins in rethinkdb

I have the following data structure stored in RethinkDB table:

{
   id: string,
   parentId: string,
   timestamp: number,
   data: Object
}

This data structure forms a tree, it can be depicted using the following diagram (white records represent ordinary data carrying records, the red ones have their data property equal to null which represents delete operation):

Diagram

Now for every record in the table I would like to be able to compute the nextRecord. Which is the closest record in time to the current one. The task seems simple when there is only one record pointing back to a parent:

1 => 2
4 => 9
5 => 6
6 => 8
...

But it becomes more difficult to compute such value when parent record is being referenced by several child records:

2 => 3
3 => 5
7 => 11

The is also case when there is no child reference in which case the result should be null (for example record #8 has no child records, and so null should be returned).

So I'm not asking to write the query itself (which on the other hand would be really great to me) but at least point out the direction in which I can find solution to this problem.

Thank you in advance!

Upvotes: 1

Views: 91

Answers (1)

mlucy
mlucy

Reputation: 5289

You can do this efficiently with a compound index on parentId and timestamp. You can create the index like this:

r.table('data').indexCreate('parent_timestamp', function(row) {
  return [row('parentId'), row('timestamp')];
})

After you've done that, you can find the earliest item with parent PARENT like so:

r.table('data')
 .between([PARENT, r.minval], [PARENT, r.maxval], {index: 'parent_timestamp'})
 .orderBy({index: 'parent_timestamp'})
 .nth(0).default(null)

Upvotes: 1

Related Questions