RethinkDB, Efficently Limit Results By Field

Question

I'm trying to limit results for each user_id field.

For example, I have a list of users Ids and i want to limit 2 friends per user from the friends selection set.

Table: users

{
  name: String,
  surname: String
}

Table: friends

{
  user_id: ObjectId // refer to user
  friend_id: ObjectId // refer to user
}

Query

The closest query i could write to achieve this is the following:

var userIds = [1,2];

r.map(userIds, function(userId) {
    return r.table('friends').filter(function(friend) {
        return userId.eq(friend('user_id'));
    }).limit(2).coerceTo('array');
});

which result:

[
 [
   {
    user_id: 1,
    friend_id: 2
   },
   {
    user_id: 1,
    friend_id: 3
   },
  ],

  [
   {
    user_id: 2,
    friend_id: 3
   },
   {
    user_id: 2,
    friend_id: 4
   },
  ]
]

am I using the right approach?

Answer 1

That works but will be slow. If you create an index on the user_id field, you can replace the filter with r.table('friends').getAll(userId, {index: 'user_id'}) which will be much faster.