Firebase Leaderboard, best Implmentation. SQL Joins and orderby

Question

I am creating a leaderboard app using Firebase as the database. I come from an old school SQL background so this new noSQL type of databasing is giving me database key and reference anxiety.

If I have groups, each groups having members, and then all the users have scores for various tests, what is the most efficient way of getting the data for a group where the scores of a given test are displayed in order. In SQL this is obviously trivial using joins and then orderby but so far in the firebase docs I can't workout how to efficiently query to get blocks of joined data without having some of the sorting work on the client side

my current solution is, given below but it seems painful to have to do all the sorting on the client side. I currently get all the users for the group, and then iterate to get the individual records of test scores for each user. I need to get the single highest score for a given test, regardless of date.

var testName = 'test1';
var scores = firebase.database().ref('/groupMembers/' + 'TestGroup').once('value').then(function(snapshot) {
                console.log(snapshot.val());
                for(var user in snapshot.val()){
                    //...query the database for each separate users score on test1
                }
            //sort the test scores here on client side js
            });

Any tips of the database design won't go amiss either!

groupMembers{
   TestGroup{
       user1 : true
       user2 : true
   }
}

testScores{
   user1{
      test1{
         date : 11/04/2016
         score : 90
      }
     test1{
         date : 05/07/2016
         score : 100
      }
     test2{
         date : 11/06/2016
         score : 50
      }
   }
   user2{
      test1{
         date : 15/04/2016
         score : 70
      }
     test2{
         date : 05/07/2016
         score : 80
      }
     test2{
         date : 11/10/2016
         score : 50
      }
   }
}

Thanks

Frank van Puffelen · Answer 1 · 2020-04-09T00:57:43.473

In NoSQL you often model the data for the way you want to access it. I highly recommend reading this article on NoSQL data modeling.

If I understand your question correctly, you want to show a leaderboard for a specific group with the top-scoring users of that group. To do that with a Firebase query, you could use this data model:

groupScores: {
  TestGroup: {
    user1: { // the highest score for user1
      test: "test1"
      date : 05/07/2016
      score : 100
    }
    user2: { // the highest score for user2
      test: "test2"
      date : 05/07/2016
      score : 80
    }
  }
}

So you'll not that I keep only the highest score for each user in this model, no matter on what test the user got that score. This way each user can only appear once in the leader board. If your use-case is different, you'll have to model the data differently.

With this data structure you can get the leaderboard with:

var query = ref.child('groupScores/TestGroup').orderByChild('score').limitToLast(10);
query.on('value', function(snapshot) {
  // TODO: loop over children, invert them, show leaderboard
});

Is there an equivalent of this in android? – Ngugi Kariuki Apr 09 '20 at 00:49 — Ngugi Kariuki, Apr 09 '20 at 00:49

score 1 · Accepted Answer · answered Sep 12 '16 at 13:28

I think your definitely going down the right path. Sorry for changing your data around a bit, not sure how you can have two keys with the same name (test1 in the first user and test2 in the second) but I would store your data like this:

{
    groupMembers : {
        TestGroup : {
            tests : {
                test1 : true,
                test2 : true
            },
            users : {
                user1 : true
                user2 : true
            }
        }
    },
    userTestScores : {
        user1 : {
            test1 : true,
            test2 : true
        },
        user2 : {
            test1 : true,
            test2 : true
        }
    },
    testScores : {
        test1 : {
            users : { 
                user1: {
                    date : "11/04/2016"
                    score : 90
                },
                user2: {
                    date : "15/04/2016"
                    score : 70                  
                }
            }
        },
        test2: {
            users: {
                user1: {
                    date : "11/06/2016"
                    score : 50
                },
                user2: {
                    date : "05/07/2016"
                    score : 80              
                }
            }
        }
    }
}

And then your query would be to find all the tests in a test group and then you can easily find the best score

firebase.database().ref('/groupMembers/TestGroup/tests').once('value').then(function (snapshot) {
    for (var test in snapshot.val()) {
        firebase.database().ref('/groupMembers/testScores/tests/' + test + '/users/').orderByChild('score').limitToLast(1).once('value').then(function (snapshot) {
            //highest score will be here
        });
    }
});

brilliant this is what I was aiming for for! – Sam Palmer Sep 12 '16 at 17:37 — Sam Palmer, Sep 12 '16 at 17:37

Firebase Leaderboard, best Implmentation. SQL Joins and orderby

2 Answers2

Linked