CODEWITHSUNDEEP
rethinkdbrethinkdb-javascript
MadLax
MadLax

Reputation: 1339

Rethinkdb The many-to-many relationship

I have tables of the following kind

entity

|id|title|

entity_authors

|id|authorName|

entity_to_authors

|id|entityId|authorId|

I managed to join the tables entity and entity_to_authors

r.table("entity")
    .get("f17234b6-b25c-4037-8dd1-314841967964")
    .merge({e -> r.hashMap("entity_to_authors", r.table("entity_to_authors").filter(r.hashMap("entityId", e.g("id"))).coerceTo("array"))})
    .run<Unit>(connection)

Now I need to get data from the table entity_authors I did it this way (but it's wrong)

r.table("entity")
    .get("f17234b6-b25c-4037-8dd1-314841967964")
    .merge({e -> r.hashMap("entity_to_authors", r.table("entity_to_authors").filter(r.hashMap("entityId", e.g("id"))).coerceTo("array"))})
    .merge({e -> r.hashMap("entity_authors", r.table("entity_authors").filter(r.hashMap("id", e.g("entity_to_authors").nth(0).g("authorId"))).coerceTo("array"))})
    .run<Unit>(connection)

since here I use nth(0) to get the first line of data received from entity_to_authors What should I do to get all the authors?

Sorry for my English!

Upvotes: 0

Views: 79

Answers (2)

Bopsi
Bopsi

Reputation: 2424

Here is one way to write the query :- 

r.table("entity")
  .merge(function(entity) {
    return {
     authorIds: r.table("entity_to_authors").filter({ entityId: entity("id")})("authorId").coerceTo("array")
    }
  })
  .merge(function(entity) {
    return {
     authors: r.table("entity_authors").getAll(r.args(entity("authorIds"))).coerceTo("array")
    }
  })
  .without("authorIds")

Step 1 : Get the mapped authorIds array from first merge.

Step 2 : Get the authors from authorIds from second merge. getAll normally takes comma separated values, to pass an array you need to use r.args.

Step 3 : Lastly, remove authorIds property from result, we don't want the mapping in our result, we want mapped authors.

Hope it helps.

Upvotes: 0

MadLax
MadLax

Reputation: 1339

I was told that this is done so:

.merge{ row -> r.hashMap("entity_authors", r.table("entity_authors") .filter { ea -> row.g("entity_to_authors").g("authorId").contains(ea.g("id")) } .coerceTo("array"))}

Upvotes: 0

Related Questions