Rethinkdb The many-to-many relationship

Question

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!

score 0 · Answer 1 · answered Mar 22 '18 at 16:16

0

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"))}

answered Mar 22 '18 at 16:16

MadLax

1,149
3
10
13

score 0 · Answer 2 · answered Jul 16 '19 at 08:26

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.

Rethinkdb The many-to-many relationship

2 Answers2