RethinkDB Table Joins

Question

I have two tables as follows one for hosts and one for users.

Hosts:

{
        "host": "server1",
        "address": "192.168.1.22", 'users': ["Magneto", "Professor Xavier", "Storm"]
    },
    {
        "host": "server2",
        "address": "192.168.1.23", 'users': ["Storm"]
    },
    {
        "host": "server3",
        "address": "192.168.1.24", 'users': ["Magneto", "Storm"]
    }

Users:

{
        "hero": "Magneto",
        "name": "Max Eisenhardt",
        "aka": ["Magnus", "Erik Lehnsherr", "Lehnsherr"],
        "magazine_titles": ["Alpha Flight", "Avengers", "Avengers West Coast"]
    },
    {
        "hero": "Professor Xavier",
        "name": "Charles Francis Xavier",
        "magazine_titles": ["Alpha Flight", "Avengers", "Bishop", "Defenders"]
    },
    {
        "hero": "Storm",
        "name": "Ororo Monroe",
        "magazine_titles": ["Amazing Spider-Man vs. Wolverine", "Excalibur",
            "Fantastic Four", "Iron Fist"]
    }

I'm trying to create a join query from the hosts to get the users to ultimately get something like this for each of the servers. Essentially combining the user data into the server document. e.g.

 {
"host": "server3",
"address": "192.168.1.24",
"users": [
    {
        "hero": "Storm",
        "name": "Ororo Monroe",
        "magazine_titles": [
            "Amazing Spider-Man vs. Wolverine",
            "Excalibur",
            "Fantastic Four",
            "Iron Fist"
        ]
    },
    {
        "hero": "Magneto",
        "name": "Max Eisenhardt",
        "aka": [
            "Magnus",
            "Erik Lehnsherr",
            "Lehnsherr"
        ],
        "magazine_titles": [
            "Alpha Flight",
            "Avengers",
            "Avengers West Coast"
        ]
    }
]
}

Since the users are contained within the servers "users" array, I can't quite work out how I should perform my query here?

This is what I've tried but no luck so far:

r.db('data').table("hosts")
 .concatMap(function (x) {
   return x("users").map(function (usr) {
   return x.merge({users: usr});
 });
}).eqJoin("users", r.db('data').table("users"))

score 1 · Answer 1 · answered Sep 30 '14 at 18:15

Create an index on hero first

 r.db('data').table('users').indexCreate("hero")

 r.db('data').table("hosts").merge(function(host) {
     return {
         users: r.table("users").getAll(r.args(host("users")), {index: "hero"}).coerceTo("ARRAY")
     }
 })

If the array users may be empty, you need to add a if statement with r.branch

 r.db('data').table("hosts").merge(function(host) {
     return r.branch(
         host("users").isEmpty(),
         {},
         {
              users: r.table("users").getAll(r.args(host("users")), {index: "hero"}).coerceTo("ARRAY")
         }
     )
 })

RethinkDB Table Joins

1 Answers1