Self join in rethinkdb

Question

I have a table such as the following (implementing tree structure) in rethinkdb:

id    label    parent
1     Node 1   
2     Node 2   1
3     Node 3   1
4     Node 4   2

I want to write a query that gives me the following output:

id    label    parent
1     Node 1   
2     Node 2   Node 1
3     Node 3   Node 1
4     Node 4   Node 2

For this, I have written the following:

r.db("exampleDB").table("exampleTable").eqJoin("parent", r.db("exampleDB").table("exampleTable")).merge(function(val) {
  return {
    "left": {
      "parent": val("right")("label")
    }
  }
}).without({
  "right": true
}).zip()

This query returns all the rows except for the ones which are root nodes. How do I get that and also I want to know if this is the most efficient way to do so because I might have to do this for much complex scenarios also.

score 0 · Answer 1 · answered Jul 29 '15 at 10:29

You can do this using inner join with some modifications:

r.db("exampleDB").table("exampleTable").innerJoin(r.db("exampleDB").table("exampleTable"), function(left, right){
  // if parent is not empty join by parent <--> id
  return left("parent").eq(right("id"))
    // or if parent is empty
    .or( left("parent").eq("").and(left("id").eq(right("id"))))
}).merge(function(val) {
  return {
    "left": {
      "parent": r.branch(
        // if row parent field is empty
        val("left")("parent").eq(""),
        // just return empty string
        "",
        // otherwise get label field of parent
        val("right")("label")
      )
    }
  }
}).without({
  "right": true
}).zip().orderBy("id")

Self join in rethinkdb

1 Answers1