0

This is my Friends Collection

[
    {
        "_id": "59e4fbcac23f38cdfa6963a8",
        "friend_id": "59e48f0af8c277d7a8886ed7",
        "user_id": "59e1d36ad17ad5ad3d0453f7",
        "__v": 0,
        "created_at": "2017-10-16T18:34:50.875Z"
    },
    {
        "_id": "59e5065f705a90cfa218c9e5",
        "friend_id": "59e48f0af8c277d7a8886edd",
        "user_id": "59e1d36ad17ad5ad3d0453f7",
        "__v": 0,
        "created_at": "2017-10-16T19:19:59.483Z"
    }
]

This is my Scores collection:

[
    {
        "_id": "59e48f0af8c277d7a8886ed8",
        "score": 19,
        "user_id": "59e48f0af8c277d7a8886ed7",
        "created_at": "2017-10-13T09:02:10.010Z"
    },
    {
        "_id": "59e48f0af8c277d7a8886ed9",
        "score": 24,
        "user_id": "59e48f0af8c277d7a8886ed7",
        "created_at": "2017-10-11T00:56:10.010Z"
    },
    {
        "_id": "59e48f0af8c277d7a8886eda",
        "score": 52,
        "user_id": "59e48f0af8c277d7a8886ed7",
        "created_at": "2017-10-24T09:16:10.010Z"
    },
]

This is my Users collection.

[
    {
        "_id": "59e48f0af8c277d7a8886ed7",
        "name": "testuser_0",
        "thumbnail": "path_0"
    },
    {
        "_id": "59e48f0af8c277d7a8886edd",
        "name": "testuser_1",
        "thumbnail": "path_1"
    },
    {
        "_id": "59e48f0af8c277d7a8886ee3",
        "name": "testuser_2",
        "thumbnail": "path_2"
    },
    {
        "_id": "59e48f0af8c277d7a8886ee9",
        "name": "testuser_3",
        "thumbnail": "path_3"
    },
]

And finally i need list of friends sorted in highscore order for a particular time period (say last 24 hours) with something like this...

[
{
"friend_id": "59e48f0af8c277d7a8886ed7",
"friend_name":"test_user_2"
"thumbnail":"image_path",
"highscore":15
},
"friend_id": "59e48f0af8c277d7a8886edd",
"friend_name":"test_user_3"
"thumbnail":"image_path",
"highscore":10
}
]

What's the best way to achieve this? I have tried aggregation pipeline but getting quite confused with working with 3 collections.

Rohit Goyal
  • 1,521
  • 3
  • 24
  • 49
  • The way the collections are done look a lot like a SQL table design. Is there a reason? A user could have a list of friends and a list of scores all embedded in the same document. Also MongoDB is not great to join tables like SQL does. – Alex Perrin Oct 17 '17 at 12:34
  • @A.P. Scores entries would keep increasing with time. A user would have 20-30 score entries per day. In case of Friends it could be embedded but won't the array size of 100-500 entries would be too big and impact performance? – Rohit Goyal Oct 17 '17 at 14:23

1 Answers1

0

Following your answers, an array size of 500 entries in a document may not be a bad idea to store the friends as you would only store "friends id" and "created" in each entry. It saves having a collection. You would not have too much performances issues if you project the data in your query by selecting only the fields you want.

https://docs.mongodb.com/v3.2/tutorial/project-fields-from-query-results/#return-specified-fields-only

For the score that increase of 30 per day; it depends what type of query you do. It would take a while to reach the 2MB limit per the document by adding 30 scores per day.

regarding joining the different collections there is a stack overflow question about it:

How do I perform the SQL Join equivalent in MongoDB?

or

https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

You will need to use the aggregation framework from mongoDB to use if; not just a find command.

Alex Perrin
  • 370
  • 2
  • 8
  • I got it working using look up aggregation. My current query time is around 400ms to get friend's leaderboard for last 24 hours when i had the collections separately with 10,000 users and 100,000 scores entries. No idea with what speed it would increase I guess i should try the array way as well to compare. – Rohit Goyal Oct 18 '17 at 12:55
  • Bench marking different collection design is always a good idea. – Alex Perrin Oct 18 '17 at 13:51