RethinkDB - Query with count and join

Question

I have two "tables" such as:

PEOPLE (ID / NAME)

1 | JOHN

2 | MARY

3 | PETER

MESSAGES (ID / PERSON_ID / TEXT)

1 | 1 | 'Text'

1 | 1 | 'Text 2'

1 | 2 | 'Text 3'

1 | 3 | 'Text 4'

How can I get the number of messages of each person? Just like:

(PERSON_ID / NAME / MESSAGES)

1 | JOHN | 2

2 | MARY | 1

3 | PETER | 1

score 1 · Accepted Answer · answered Jul 26 '16 at 21:20

this should do the trick:

r.db("so").table("messages")
  .group(r.row('person_id')).count().ungroup()
  .map((result) => {
    return result.merge(r.db("so").table("users").get(result('group')));
  })

Result looks like this:

[
  {"group":1,"id":1,"name":"Dalan","reduction":2},
  {"group":2,"id":2,"name":"Rodger","reduction":3}
]

You can further rename the fields as you like with the .merge method but this gets you the join and grouping that you wanted!

Let me know if you have any questions.

RethinkDB - Query with count and join

1 Answers1