Sorting Via Average of Two Database Children

GameForGrants

Hello,

I have a DataTable that is acting as a leaderboard. I have two scores coming directly from a mobile application. My goal is to get a weighted (average) score from the two games producing scores. I already have the DataTable producing the average score, but it is not sorting correctly.
Is there any way to sort via the average of two children without creating an entirely new entry in my database.

Here is my current code:

         $(function () {
            t1 = $('#table').DataTable({
                responsive: true,
                searching: false,
                "paging": false,
                lengthChange: false,
                "info": false,
                "order": [[0, "asc"]],
                columnDefs: [{
                    targets: 1,
                    "render": function ( data, type, row, meta ) {
                        return data.split('@')[0];
                    }
                }]
                
            
                
            });
            t2 = $('#table2').DataTable({
                responsive: true,
                searching: false,
                "paging": false,
                "info": false,

                lengthChange: false,
                "order": [[0, "asc"]],
                columnDefs: [{
                    targets: 1,
                    "render": function ( data, type, row, meta ) {
                        return data.split('@')[0];
                    }
                }]
            });

            t3 = $('#table3').DataTable({
                responsive: true,
                searching: false,
                "paging": false,
                "info": false,

                lengthChange: false,
                "order": [[0, "asc"]],
                columnDefs: [{
                    targets: 1,
                    "render": function ( data, type, row, meta ) {
                        return data.split('@')[0];
                    }
                }]
            });
            var sr = 20,sr2=20,sr3=20;
            firebase.database().ref("/players").orderByChild("score1").limitToLast(20).on("child_added", function (snp) {
                
                console.log(snp.val());
                t1.row.add([
                sr,
                snp.val().name,
                snp.val().score1
                ]).draw(false);
                
              

                        sr--;           
            })
            
            firebase.database().ref("/players").orderByChild("score2").limitToLast(20).on("child_added", function (snp) {
                
                console.log(snp.val());
                t2.row.add([
                sr2,
                snp.val().name,
                snp.val().score2
                ]).draw(false);
                
              

                        sr2--;          
            })     
            firebase.database().ref("/players").orderByChild(("score1"+"score2")/2).limitToLast(20).on("child_added", function (snp) {

                console.log(snp.val());
                t3.row.add([
                sr3,
                snp.val().name,(
                parseInt(snp.val().score1) + parseInt(snp.val().score2))/2
                ]).draw(false);


                sr3--;
            })
        })

With the following being the portion that does not exist in my database:

firebase.database().ref("/players").orderByChild(("score1"+"score2")/2).limitToLast(20).on("child_added", function (snp) {

                console.log(snp.val());
                t3.row.add([
                sr3,
                snp.val().name,(
                parseInt(snp.val().score1) + parseInt(snp.val().score2))/2
                ]).draw(false);


                sr3--;
            })
        })

Thanks in advance for any help!

colin

Hi @GameForGrants ,

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

GameForGrants

My apologies, the link is https://gameforgrants.org/view-leaderboards

colin

Hi @GameForGrants ,

It's probably not sorting because there are errors - "Can't reinitialise DataTable". It would be worth addressing that first,

Cheers,

Colin

GameForGrants

That is odd, I am not getting that error on various web browsers. Either way, when i use the code above, the data is not displayed at all. The two tables that are just using one child are displaying and sorting fine.

kthorngren

I'm not following what the exact problem is. When I open the link I see three Datatables but also see the "Can't reinitialise DataTable" error. Sounds like the example you provided doesn't show the issue regardless of the error. Also the page's source code is not easy to look at.

What exactly do you mean by child? In Datatables terms I think of this example or the solution described here with one Datatable as the parent and the other as a child. But it doesn't seem that is the case for your site.

Maybe you can put together a simple example replicating the issue so that we can look at to help debug. Put examples of the score data in Javascript variables that can be used as the data for the three Datatables. Please make sure to let us know what exactly to look at for the sorting issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

I put together a very simple example of your table3 and the average seems to work. Maybe you can update it to replicate the issue.

Kevin

GameForGrants

Perhaps the complication is coming from the fact that I am pulling from a Firebase database. This also may be why my source code is not so easy to look at.

I did end up getting the error. The problem was that all of my browsers were logged into my WordPress account so that was stopping the errors from arising.
My first step now it appears is to figure out why I am receiving the "Can't reinitialize DataTable" error.

Regardless, I do not believe the code I have in there will sort the 3rd table. My goal is to get an average of two values from the DataTable and sort via those values, while my code is only able to sort the Firebase data. I am not really sure what my next step is here or what code would allow me to achieve this.

kthorngren

Looks like (parseInt(snp.val().score1) + parseInt(snp.val().score2))/2 should result in all data in that column to be integers. However if you end up with NaN or something else then, I think, the column will be treated as text instead of numeric. Which could result in the sorting to not be correct.

I line 80 of your fist code snippet I would change the console.log statement to something like this:
console.log(typeof (parseInt(snp.val().score1) + parseInt(snp.val().score2))/2)

Or something similar to debug the calculation. My example shows that your Javascript code should work. The result will be based on the actual data. Maybe you can capture your data and put it in my example.

Kevin

kthorngren

Just noticed I didn't provide a link to my example:
http://live.datatables.net/kiyihehi/1/edit

Kevin

GameForGrants

Ok, so I have tried to change the sorting using your example. It still does not work. But now I have noticed another table is not working. I am assuming this has to do with the error that is popping up (cannot reinitialize DataTable), however, I cannot seem to find the error in the code. Could you please help me pinpoint what may be causing this?
Here is the complete code:

        $(function () {
            t1 = $('#table').DataTable({
                responsive: true,
                searching: false,
                "paging": false,
                lengthChange: false,
                "info": false,
                "order": [[0, "asc"]],
                columnDefs: [{
                    targets: 1,
                    "render": function ( data, type, row, meta ) {
                        return data.split('@')[0];
                    }
                }]
                
            
                
            });
            t2 = $('#table2').DataTable({
                responsive: true,
                searching: false,
                "paging": false,
                "info": false,

                lengthChange: false,
                "order": [[0, "asc"]],
                columnDefs: [{
                    targets: 1,
                    "render": function ( data, type, row, meta ) {
                        return data.split('@')[0];
                    }
                }]
            });

            t3 = $('#table3').DataTable({
                responsive: true,
                searching: false,
                "paging": false,
                "info": false,

                lengthChange: false,
                "order": [[0, "asc"]],
                columnDefs: [{
                    targets: 1,
                    "render": function ( data, type, row, meta ) {
                        return data.split('@')[0];
                    }
                }]
            });
            var sr = 20,sr2=20,sr3=20;
            firebase.database().ref("/players").orderByChild("score1").limitToLast(20).on("child_added", function (snp) {
                
                console.log(snp.val());
                t1.row.add([
                sr,
                snp.val().name,
                snp.val().score1
                ]).draw(false);
                
              

                        sr--;           
            })
            
            firebase.database().ref("/players").orderByChild("score2").limitToLast(20).on("child_added", function (snp) {
                
                console.log(snp.val());
                t2.row.add([
                sr2,
                snp.val().name,
                snp.val().score2
                ]).draw(false);
                
              

                        sr2--;          
            })     
            firebase.database().ref("/players").limitToLast(20).on("child_added", function (snp) {

                console.log(typeof (parseInt(snp.val().score1) + parseInt(snp.val().score2))/2);
                t3.row.add([
                sr3,
                snp.val().name,(
                parseInt(snp.val().score1) + parseInt(snp.val().score2))/2
                ]).draw(false);


                sr3--;
            })
        })
        
        </script>
        <style>
        td{
            text-align:justify;
        }
        body {
    font-family: montserrat;
    
}
        caption {
    text-align: center;
    margin-bottom: 5px;
    text-transform: uppercase;
    font-size: 160%;
    padding: 5px;
    letter-spacing: 5px;
    font-weight: bold;
}
       
        </style>
    </head>
        <body>
            <div style="margin:20px">
                <table class="" style="width:100%" id="table">
                    
                        <caption>10<sup>Squared</sup></caption>
                    <thead>
                        <tr>
                    <th>Rank</th>
                    <th>User Name</th>
                    <th>Score</th>
                    </tr>

                    </thead>
                    <tbody id="tbody"></tbody>
                </table>
            </div>
            <div style="margin:20px">
                <table class="" style="width:100%" id="table2">
                    <caption>Quick Math</caption>
                    <thead>
                        <tr>
                    <th>Rank</th>
                    <th>User Name</th>
                    <th>Score</th>
                    </tr>

                    </thead>
                    <tbody id="tbody2"></tbody>
                </table>
            </div>
            <div style="margin:20px">
                <table class="" style="width:100%" id="table3">
                    <caption>Weighted Score</caption>
                    <thead>
                        <tr>
                    <th>Rank</th>
                    <th>User Name</th>
                    <th>Score</th>
                    </tr>
                    </thead>
                    <tbody id="tbody3"></tbody>
                </table>
            </div>
        </body>
</html>

kthorngren

The page isn't coming up for me now.

The link in the error message provides the details and info of how to fix:
https://datatables.net/manual/tech-notes/3

Have you followed those steps yet?

Kevin

GameForGrants

Thanks for responding. I did follow those links, I just can't seem to figure out what I have that is conflicting.
And I apologize, I have since changed the link. The new link is: https://www.gameforgrants.org/games

colin

The table is still getting the same error as before: "Can't reinitialise DataTable". The code you posted above isn't reinitialising the tables - each one is only being done once. But that is wrapped in a function - is it possible the function is being called twice?

GameForGrants

Here is the code in its entirety. I cannot find where a function is being called twice.

<html>
    <head>
        <title>Game Score</title>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
        <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/rowreorder/1.2.5/js/dataTables.rowReorder.min.js"></script>
        <script src="https://cdn.datatables.net/responsive/2.2.3/js/dataTables.responsive.min.js"></script>
        <script src="https://www.gstatic.com/firebasejs/6.3.4/firebase-app.js"></script>
        <script src="https://www.gstatic.com/firebasejs/6.3.3/firebase-database.js"></script>
        <link rel="stylesheet" href="
       https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css
              " />
        <link rel="stylesheet" href="
       https://cdn.datatables.net/rowreorder/1.2.5/css/rowReorder.dataTables.min.css      " />
        <link rel="stylesheet" href="
        https://cdn.datatables.net/responsive/2.2.3/css/responsive.dataTables.min.css      " />
         <script>
        var firebaseConfig = {
            apiKey: "",
            authDomain: "",
            databaseURL: "",
            projectId: "",
            storageBucket: "",
            messagingSenderId: "",
            appId: ""
        };

        firebase.initializeApp(firebaseConfig);
      
        $(function () {
            t1 = $('#table').DataTable({
                responsive: true,
                searching: false,
                "paging": false,
                lengthChange: false,
                "info": false,
                "order": [[0, "asc"]],
                columnDefs: [{
                    targets: 1,
                    "render": function ( data, type, row, meta ) {
                        return data.split('@')[0];
                    }
                }]
                
            
                
            });
            t2 = $('#table2').DataTable({
                responsive: true,
                searching: false,
                "paging": false,
                "info": false,

                lengthChange: false,
                "order": [[0, "asc"]],
                columnDefs: [{
                    targets: 1,
                    "render": function ( data, type, row, meta ) {
                        return data.split('@')[0];
                    }
                }]
            });

            t3 = $('#table3').DataTable({
                responsive: true,
                searching: false,
                "paging": false,
                "info": false,

                lengthChange: false,
                "order": [[0, "asc"]],
                columnDefs: [{
                    targets: 1,
                    "render": function ( data, type, row, meta ) {
                        return data.split('@')[0];
                    }
                }]
            });
            var sr = 20,sr2=20,sr3=20;
            firebase.database().ref("/players").orderByChild("score1").limitToLast(20).on("child_added", function (snp) {
                
                console.log(snp.val());
                t1.row.add([
                sr,
                snp.val().name,
                snp.val().score1
                ]).draw(false);
                
              

                        sr--;           
            })
            
            firebase.database().ref("/players").orderByChild("score2").limitToLast(20).on("child_added", function (snp) {
                
                console.log(snp.val());
                t2.row.add([
                sr2,
                snp.val().name,
                snp.val().score2
                ]).draw(false);
                
              

                        sr2--;          
            })     
            firebase.database().ref("/players").limitToLast(20).on("child_added", function (snp) {

                console.log(typeof (parseInt(snp.val().score1) + parseInt(snp.val().score2))/2);
                t3.row.add([
                sr3,
                snp.val().name,(
                parseInt(snp.val().score1) + parseInt(snp.val().score2))/2
                ]).draw(false);


                sr3--;
            })
        })
        
        </script>
        <style>
        td{
            text-align:justify;
        }
        body {
    font-family: montserrat;
    
}
        caption {
    text-align: center;
    margin-bottom: 5px;
    text-transform: uppercase;
    font-size: 160%;
    padding: 5px;
    letter-spacing: 5px;
    font-weight: bold;
}
       
        </style>
    </head>
        <body>
            <div style="margin:20px">
                <table class="" style="width:100%" id="table">
                    
                        <caption>10<sup>Squared</sup></caption>
                    <thead>
                        <tr>
                    <th>Rank</th>
                    <th>User Name</th>
                    <th>Score</th>
                    </tr>

                    </thead>
                    <tbody id="tbody"></tbody>
                </table>
            </div>
            <div style="margin:20px">
                <table class="" style="width:100%" id="table2">
                    <caption>Quick Math</caption>
                    <thead>
                        <tr>
                    <th>Rank</th>
                    <th>User Name</th>
                    <th>Score</th>
                    </tr>

                    </thead>
                    <tbody id="tbody2"></tbody>
                </table>
            </div>
            <div style="margin:20px">
                <table class="" style="width:100%" id="table3">
                    <caption>Weighted Score</caption>
                    <thead>
                        <tr>
                    <th>Rank</th>
                    <th>User Name</th>
                    <th>Score</th>
                    </tr>
                    </thead>
                    <tbody id="tbody3"></tbody>
                </table>
            </div>
        </body>
</html>

kthorngren

Im not familiar with your environment but looking at the source I see this listed twice in your page:

<script src="https://www.gameforgrants.org/wp-content/cache/min/1/3c5b9046003bf12686695e73c650f822.js" data-minify="1" defer></script>

That file looks to contain the Datatables init code and looks like it is executed twice.

Kevin

GameForGrants

Thank you so much! It turned out to be my caching plugin that was causing the issue. My site now ranks lower on Google's PageSpeed, but I can address that later.

My problem now is that the code is still not sorting the scores correctly. Any ideas?

kthorngren

The link isn't working. Did you look at the output of this debug?

            firebase.database().ref("/players").limitToLast(20).on("child_added", function (snp) {
 
                console.log(typeof (parseInt(snp.val().score1) + parseInt(snp.val().score2))/2);
                t3.row.add([
                sr3,
                snp.val().name,(
                parseInt(snp.val().score1) + parseInt(snp.val().score2))/2
                ]).draw(false);
 
 
                sr3--;
            })

You can add others to try understanding the data and if its what you expect.

Kevin