How to join results on a firebase query the right way

Question

I am using Firebase real-time database for my app build with Unity. In order to build a "friend" leaderboard, the database will keep track of users their friends and their scores.

The database has the following structure:

scores{
user id : score
}
Users:{
    Id: {
        ageRange
        email
        name
        friendlist : {
             multiple friends user ids
        }
    }
}

The problem is in order to get the scores and the names of every friend the app has to make alot of api calls. Atleast if I correctly understand firebase. If the user has 10 friends it will take 21 calls before the leaderboard is filled.

I came up with the following code written in c#:

List<UserScore> leaderBoard = new List<UserScore>();
db.Child("users").Child(uid).Child("friendList").GetValueAsync().ContinueWith(task => {
    if (task.IsCompleted)
    {
        //foreach friend
        foreach(DataSnapshot h in task.Result.Children)
        {
            string curName ="";
            int curScore = 0;
            //get his name in the user table
            db.Child("users").Child(h.Key).GetValueAsync().ContinueWith(t => {
                if (t.IsCompleted)
                {
                    DataSnapshot s = t.Result;
                    curName = s.Child("name").Value.ToString();
                    //get his score from the scores table
                    db.Child("scores").Child(h.Key).GetValueAsync().ContinueWith(q => {
                        if (q.IsCompleted)
                        {
                            DataSnapshot b = q.Result;
                            curScore = int.Parse(b.Value.ToString());
                            //make new userscore and add to leaderboard
                            leaderBoard.Add(new UserScore(curName, curScore));
                            Debug.Log(curName);
                            Debug.Log(curScore.ToString());
                        }
                    });
                }
            });
        }
    }
});

Is there any other way to do this? I've read multiple stack overflow questions watched firebase tutorials but i didnt found any simpler or more efficient way to get the job done.

Answer 1

There's not a way of reducing the number of API calls without duplicating data/restructuring your database. However, reducing API calls doesn't necessarily mean the overall read strategy is faster/better. My suggestion would be to optimize your reading strategy to reduce overall data read and to make reads concurrently when possible.

Solution 1: Optimize reading strategy

This is my recommended solution, because it doesn't include extra unnecessary data nor does it include managing consistency of your data.

Your code should look something like below:
(DISCLAIMER: I'm not a C# programmer, so there might be some errors)

List<UserScore> leaderBoard = new List<UserScore>();
db.Child("users").Child(uid).Child("friendList").GetValueAsync().ContinueWith(task => {
    if (task.IsCompleted)
    {
        //foreach friend
        foreach(DataSnapshot h in task.Result.Children)
        {
            // kick off the task to retrieve friend's name
            Task nameTask = db.Child("users").Child(h.Key).Child("name").GetValueAsync();
            // kick off the task to retrieve friend's score
            Task scoreTask = db.Child("scores").Child(h.Key).GetValueAsync();
            // join tasks into one final task
            Task finalTask = Task.Factory.ContinueWhenAll((new[] {nameTask, scoreTask}), tasks => {
              if (nameTask.IsCompleted && scoreTask.IsCompleted) {
                // both tasks are complete; add new record to leaderboard
                string name = nameTask.Result.Value.ToString();
                int score = int.Parse(scoreTask.Result.Value.ToString());
                leaderBoard.Add(new UserScore(name, score));
                Debug.Log(name);
                Debug.Log(score.ToString());
              }
            })
        }
    }
});

The above code improves the overall read strategy by not pulling all of a friend's user data (i.e. name, email, friendlist, etc.) and by pulling the name concurrently with score.

Solution 2: Duplicate name to scores table

If this still isn't optimal enough, you can always duplicate the friend's name in their score table. Something like below:

scores: {
  <user_id>: {
    name: <user_name>,
    score: <user_score>
  }
}

This would then allow you to only make one call per friend instead of two. However, you will still be reading the same amount of data, and you will have to manage the consistency of the data (either use a Firebase Function to propagate user name changes or write to both places).

Solution 3: Combine scores table into users table

If you don't want to manage the consistency issue, you can just combine the scores table into the users table.
Your structure would be something like:

users: {
  <user_id>: {
    name: <user_name>,
    ...,
    score: <user_score>
  }
}

However, in this instance, you will be reading more data (email, friendlist, etc.)

I hope this helps.

Answer 2

While the following will not reduce the number of calls, it will create tasks for the retrieval of the data and run them all simultaneously, returning the list of desired user scores.

var friendList = await db.Child("users").Child(uid).Child("friendList").GetValueAsync();

List<Task<UserScore>> tasks = new List<Task<UserScore>>();
//foreach friend
foreach(DataSnapshot friend in friendList.Children) {
    var task = Task.Run( async () => {
        var friendKey = friend.Key;
        //get his name in the user table
        var getName = db.Child("users").Child(friendKey).Child("name").GetValueAsync();
        //get his score from the scores table
        var getScore = db.Child("scores").Child(friendKey).GetValueAsync();

        await Task.WhenAll(getName, getScore);

        var name = getName.Result.Value.ToString();
        var score = int.Parse(getScore.Result.Value.ToString());

        //make new userscore to add to leader board
        var userScore = new UserScore(name, score);
        Debug.Log($"{name} : {score}");
        return userScore;
    });
    tasks.Add(task);
}

var scores = await Task.WhenAll(tasks);

List<UserScore> leaderBoard = new List<UserScore>(scores);

Answer 3

This is mostly database structure issue.

First, you need leaderboard table.

leaderboard: {
    <user_id>: <score>
}

Second, you need users table.

users: {
    <user_id>: {
        name: <user_name>,
        ...,
        score: <user_score>,
        friendlist: {
            multiple friends user ids
        }
    }
}

And you have to update leaderboard's score and users' score at the same time.

If you want to avoid Callback hell.

You can also try like this. (This code is JAVA)

// Create a new ThreadPoolExecutor with 2 threads for each processor on the
// device and a 60 second keep-alive time.
int numCores = Runtime.getRuntime().availableProcessors();
ExecutorService executor = new ThreadPoolExecutor(
        numCores * 2,
        numCores * 2,
        60L,
        TimeUnit.SECONDS,
        new LinkedBlockingQueue<Runnable>()
);

Tasks.call(executor, (Callable<Void>) () -> {
    Task<Token> getStableTokenTask = NotificationUtil.getStableToken(token);
    Token stableToken;
    stableToken = Tasks.await(getStableTokenTask);

    if (stableToken != null) {
        Task<Void> updateStableTokenTask = NotificationUtil.updateStableToken(stableToken.getId(), versionCode, versionName);
        Tasks.await(updateStableTokenTask);
    }

    if (stableToken == null) {
        Token newToken = new Token(token, versionCode, versionName);
        Task<Void> insertStableTokenTask = NotificationUtil.insertStableToken(newToken);
        Tasks.await(insertStableTokenTask);
    }

    return null;
}).continueWith((Continuation<Void, Void>) task -> {
    if (!task.isSuccessful()) {
        // You will catch every exceptions from here.
        Log.w(TAG, task.getException());
        return null;
    }
    return null;
});