Quickstart-unity: Realtime Database Query data (Sorting and filtering) not working or is ignored

[hamidhomatash]

[REQUIRED] Please fill in the following fields:

• Unity editor version: 2020.1.4f1
• Firebase Unity SDK version: 7.0.2
• Source you installed the SDK: .unitypackage
• Problematic Firebase Component: Database
• Other Firebase Components in use: None
• Additional SDKs you are using: None
• Platform you are using the Unity editor on: Windows
• Platform you are targeting: iOS and Android
• Scripting Runtime: IL2CPP

[REQUIRED] Please describe the issue here:

None of the query data commands work on a DatabaseReference. Trying to use any of the following functions causes no filtering to be done on the data received: OrderByChild(), LimitToFirst(), LimitToLast(), StartAt(), EndAt(), EqualTo(). No errors or exceptions are thrown either.

I have not tried OrderByKey() or OrderByValue() but I would presume these to have the same issue. This means that when querying data on a node, all child nodes from the DatabaseReference node are returned instead of just the expected filtered results. This means I get all nodes lexicographically by key regardless of what query functions I have set up on the DataebaseReference.

There is something in the Unity Firebase Realtime Database SDK that is ignoring these query functions and causing all data to be sent back to the client app. I have tried the JavaScript SDK and this works as expected on the same database and I have even tried the REST API from inside Unity using UnityWebRequests and this works as expected too.

Before showing code I guess I'll explain what I'm trying to do to give context. I'm just trying to read the top 5 scores from a simple leaderboard. Players in my game can play against an AI opponent and I want to return the best 5 scores achieved. The database layour and values are:

{
  "leaderboards" : {
    "ai" : {
      "test0" : {
        "name" : "some name 0",
        "score" : 0
      },
      "test1" : {
        "name" : "some name 1",
        "score" : 100
      },
      "test10" : {
        "name" : "some name 10",
        "score" : 1000
      },
      "test11" : {
        "name" : "some name 11",
        "score" : 1100
      },
      "test12" : {
        "name" : "some name 12",
        "score" : 1200
      },
      "test13" : {
        "name" : "some name 13",
        "score" : 1300
      },
      "test14" : {
        "name" : "some name 14",
        "score" : 1400
      },
      "test15" : {
        "name" : "some name 15",
        "score" : 1500
      },
      "test16" : {
        "name" : "some name 16",
        "score" : 1600
      },
      "test17" : {
        "name" : "some name 17",
        "score" : 1700
      },
      "test18" : {
        "name" : "some name 18",
        "score" : 1800
      },
      "test19" : {
        "name" : "some name 19",
        "score" : 1900
      },
      "test2" : {
        "name" : "some name 2",
        "score" : 200
      },
      "test3" : {
        "name" : "some name 3",
        "score" : 300
      },
      "test4" : {
        "name" : "some name 4",
        "score" : 400
      },
      "test5" : {
        "name" : "some name 5",
        "score" : 500
      },
      "test6" : {
        "name" : "some name 6",
        "score" : 600
      },
      "test7" : {
        "name" : "some name 7",
        "score" : 700
      },
      "test8" : {
        "name" : "some name 8",
        "score" : 800
      },
      "test9" : {
        "name" : "some name 9",
        "score" : 900
      }
    }
  }
}

Security rules are:

{
  "rules":
  {
    ".read": "true",
    ".write": "true",
    "leaderboards":
    {
      "ai":
      {
        ".indexOn": ["score"]
      }
    }
  }
}

To know what data is returned I'm using the function GetRawJsonValue() on the DataSnapshot returned from a call to GetValueAsync(). I also tried the listener approach with the ValueChanged Event and I have debugged the values returned in Visual Studio to confirm.

NOTE: I have tested inside Unity's play mode for windows PC build and windows Android build and I have deployed a build to Android to check to see if it was a Unity Editor thing, but all give me the same result.

Steps to reproduce:

1. Create a database with above described structure and security rules
2. Install the firebase realtime database Unity package into the quick start guide example code and setup up for your test project
3. Add the following code to the function GUIDisplayControls() in the class UIHandler after the "Go Online" button (Alternatively replace the code in UIHandler.cs with appended code at the very bottom of this post):

if (GUILayout.Button("Read values"))
{
    var top5 = Firebase.Database.FirebaseDatabase.DefaultInstance.GetReference("leaderboards/ai");
    top5.KeepSynced(true);
    top5.OrderByChild("score").LimitToFirst(5);
    top5.ValueChanged += (sender, args) =>
    {
        if (args.DatabaseError != null)
        {
            Debug.LogError(args.DatabaseError.Message);
            return;
        }

        DebugLog(args.Snapshot.GetRawJsonValue());
    };

    var top5Attempt2 = Firebase.Database.FirebaseDatabase.DefaultInstance.GetReference("leaderboards/ai");
    top5Attempt2.KeepSynced(true);
    top5Attempt2.StartAt(1500);
    top5Attempt2.OrderByChild("score").StartAt(1500);    // 1500 is start of top 5 with score of 1900 being top
    top5Attempt2.GetValueAsync().ContinueWith(task =>
    {
        if (task.Exception != null)
        {
            throw task.Exception;
        }
        if (!task.IsCompleted)
        {
            return;
        }

        DebugLog(task.Result.GetRawJsonValue());
    });
}

1. Observer the data returned from the DebugLog call to be the following which is incorrect as no ordering or limits have been applied - therte should only be 5 users not all 20:

{
  "test0": {
    "name": "some name 0",
    "score": 0
  },
  "test1": {
    "name": "some name 1",
    "score": 100
  },
  "test10": {
    "name": "some name 10",
    "score": 1000
  },
  "test11": {
    "name": "some name 11",
    "score": 1100
  },
  "test12": {
    "name": "some name 12",
    "score": 1200
  },
  "test13": {
    "name": "some name 13",
    "score": 1300
  },
  "test14": {
    "name": "some name 14",
    "score": 1400
  },
  "test15": {
    "name": "some name 15",
    "score": 1500
  },
  "test16": {
    "name": "some name 16",
    "score": 1600
  },
  "test17": {
    "name": "some name 17",
    "score": 1700
  },
  "test18": {
    "name": "some name 18",
    "score": 1800
  },
  "test19": {
    "name": "some name 19",
    "score": 1900
  },
  "test2": {
    "name": "some name 2",
    "score": 200
  },
  "test3": {
    "name": "some name 3",
    "score": 300
  },
  "test4": {
    "name": "some name 4",
    "score": 400
  },
  "test5": {
    "name": "some name 5",
    "score": 500
  },
  "test6": {
    "name": "some name 6",
    "score": 600
  },
  "test7": {
    "name": "some name 7",
    "score": 700
  },
  "test8": {
    "name": "some name 8",
    "score": 800
  },
  "test9": {
    "name": "some name 9",
    "score": 900
  }
}

Have you been able to reproduce this issue with just the Firebase Unity quickstarts (this GitHub project)? Yes
What's the issue repro rate? 100%

Relevant Code:

Replace the code file UIHandler.cs in the firebase quickstart-unity sample code with the following for a full reproducible test:

// Copyright 2016 Google Inc. All rights reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

namespace Firebase.Sample.Database {
  using Firebase;
  using Firebase.Database;
  using Firebase.Extensions;
  using System;
  using System.Collections;
  using System.Collections.Generic;
  using UnityEngine;

  // Handler for UI buttons on the scene.  Also performs some
  // necessary setup (initializing the firebase app, etc) on
  // startup.
  public class UIHandler : MonoBehaviour {

    ArrayList leaderBoard = new ArrayList();
    Vector2 scrollPosition = Vector2.zero;
    private Vector2 controlsScrollViewVector = Vector2.zero;

    public GUISkin fb_GUISkin;

    private const int MaxScores = 5;
    private string logText = "";
    private string email = "";
    private int score = 100;
    private Vector2 scrollViewVector = Vector2.zero;
    protected bool UIEnabled = true;

    const int kMaxLogSize = 16382;
    DependencyStatus dependencyStatus = DependencyStatus.UnavailableOther;
    protected bool isFirebaseInitialized = false;

    // When the app starts, check to make sure that we have
    // the required dependencies to use Firebase, and if not,
    // add them if possible.
    protected virtual void Start() {
      leaderBoard.Clear();
      leaderBoard.Add("Firebase Top " + MaxScores.ToString() + " Scores");

      FirebaseApp.CheckAndFixDependenciesAsync().ContinueWithOnMainThread(task => {
        dependencyStatus = task.Result;
        if (dependencyStatus == DependencyStatus.Available) {
          InitializeFirebase();
        } else {
          Debug.LogError(
            "Could not resolve all Firebase dependencies: " + dependencyStatus);
        }
      });
    }

    // Initialize the Firebase database:
    protected virtual void InitializeFirebase() {
      FirebaseApp app = FirebaseApp.DefaultInstance;
      ////StartListener();
      isFirebaseInitialized = true;
    }

    protected void StartListener() {
      FirebaseDatabase.DefaultInstance
        .GetReference("Leaders").OrderByChild("score")
        .ValueChanged += (object sender2, ValueChangedEventArgs e2) => {
          if (e2.DatabaseError != null) {
            Debug.LogError(e2.DatabaseError.Message);
            return;
          }
          Debug.Log("Received values for Leaders.");
          string title = leaderBoard[0].ToString();
          leaderBoard.Clear();
          leaderBoard.Add(title);
          if (e2.Snapshot != null && e2.Snapshot.ChildrenCount > 0) {
            foreach (var childSnapshot in e2.Snapshot.Children) {
              if (childSnapshot.Child("score") == null
              || childSnapshot.Child("score").Value == null) {
                Debug.LogError("Bad data in sample.  Did you forget to call SetEditorDatabaseUrl with your project id?");
                break;
              } else {
                Debug.Log("Leaders entry : " +
                childSnapshot.Child("email").Value.ToString() + " - " +
                childSnapshot.Child("score").Value.ToString());
                leaderBoard.Insert(1, childSnapshot.Child("score").Value.ToString()
                + "  " + childSnapshot.Child("email").Value.ToString());
              }
            }
          }
        };
    }

    // Exit if escape (or back, on mobile) is pressed.
    protected virtual void Update() {
      if (Input.GetKeyDown(KeyCode.Escape)) {
        Application.Quit();
      }
    }

    // Output text to the debug log text field, as well as the console.
    public void DebugLog(string s) {
      Debug.Log(s);
      logText += s + "\n";

      while (logText.Length > kMaxLogSize) {
        int index = logText.IndexOf("\n");
        logText = logText.Substring(index + 1);
      }

      scrollViewVector.y = int.MaxValue;
    }

    // A realtime database transaction receives MutableData which can be modified
    // and returns a TransactionResult which is either TransactionResult.Success(data) with
    // modified data or TransactionResult.Abort() which stops the transaction with no changes.
    TransactionResult AddScoreTransaction(MutableData mutableData) {
      List<object> leaders = mutableData.Value as List<object>;

      if (leaders == null) {
        leaders = new List<object>();
      } else if (mutableData.ChildrenCount >= MaxScores) {
        // If the current list of scores is greater or equal to our maximum allowed number,
        // we see if the new score should be added and remove the lowest existing score.
        long minScore = long.MaxValue;
        object minVal = null;
        foreach (var child in leaders) {
          if (!(child is Dictionary<string, object>))
            continue;
          long childScore = (long)((Dictionary<string, object>)child)["score"];
          if (childScore < minScore) {
            minScore = childScore;
            minVal = child;
          }
        }
        // If the new score is lower than the current minimum, we abort.
        if (minScore > score) {
          return TransactionResult.Abort();
        }
        // Otherwise, we remove the current lowest to be replaced with the new score.
        leaders.Remove(minVal);
      }

      // Now we add the new score as a new entry that contains the email address and score.
      Dictionary<string, object> newScoreMap = new Dictionary<string, object>();
      newScoreMap["score"] = score;
      newScoreMap["email"] = email;
      leaders.Add(newScoreMap);

      // You must set the Value to indicate data at that location has changed.
      mutableData.Value = leaders;
      return TransactionResult.Success(mutableData);
    }

    public void AddScore() {
      if (score == 0 || string.IsNullOrEmpty(email)) {
        DebugLog("invalid score or email.");
        return;
      }
      DebugLog(String.Format("Attempting to add score {0} {1}",
        email, score.ToString()));

      DatabaseReference reference = FirebaseDatabase.DefaultInstance.GetReference("Leaders");

      DebugLog("Running Transaction...");
      // Use a transaction to ensure that we do not encounter issues with
      // simultaneous updates that otherwise might create more than MaxScores top scores.
      reference.RunTransaction(AddScoreTransaction)
        .ContinueWithOnMainThread(task => {
          if (task.Exception != null) {
            DebugLog(task.Exception.ToString());
          } else if (task.IsCompleted) {
            DebugLog("Transaction complete.");
          }
        });
    }

    // Render the log output in a scroll view.
    void GUIDisplayLog() {
      scrollViewVector = GUILayout.BeginScrollView(scrollViewVector);
      GUILayout.Label(logText);
      GUILayout.EndScrollView();
    }

    // Render the buttons and other controls.
    void GUIDisplayControls() {
      if (UIEnabled) {
        controlsScrollViewVector =
            GUILayout.BeginScrollView(controlsScrollViewVector);
        GUILayout.BeginVertical();
        GUILayout.BeginHorizontal();
        GUILayout.Label("Email:", GUILayout.Width(Screen.width * 0.20f));
        email = GUILayout.TextField(email);
        GUILayout.EndHorizontal();

        GUILayout.Space(20);

        GUILayout.BeginHorizontal();
        GUILayout.Label("Score:", GUILayout.Width(Screen.width * 0.20f));
        int.TryParse(GUILayout.TextField(score.ToString()), out score);
        GUILayout.EndHorizontal();

        GUILayout.Space(20);

        if (!String.IsNullOrEmpty(email) && GUILayout.Button("Enter Score")) {
          AddScore();
        }

        GUILayout.Space(20);

        if (GUILayout.Button("Go Offline")) {
          FirebaseDatabase.DefaultInstance.GoOffline();
        }

        GUILayout.Space(20);

        if (GUILayout.Button("Go Online")) {
          FirebaseDatabase.DefaultInstance.GoOnline();
        }

                if (GUILayout.Button("Read values"))
                {
                    var top5 = Firebase.Database.FirebaseDatabase.DefaultInstance.GetReference("leaderboards/ai");
                    top5.KeepSynced(true);
                    top5.OrderByChild("score").LimitToFirst(5);
                    top5.ValueChanged += (sender, args) =>
                    {
                        if (args.DatabaseError != null)
                        {
                            Debug.LogError(args.DatabaseError.Message);
                            return;
                        }

                        DebugLog(args.Snapshot.GetRawJsonValue());
                    };

                    var top5Attempt2 = Firebase.Database.FirebaseDatabase.DefaultInstance.GetReference("leaderboards/ai");
                    top5Attempt2.KeepSynced(true);
                    top5Attempt2.OrderByChild("score").StartAt(1500);    // Get 5 before
                    top5Attempt2.GetValueAsync().ContinueWith(task =>
                    {
                        if (task.Exception != null)
                        {
                            throw task.Exception;
                        }
                        if (!task.IsCompleted)
                        {
                            return;
                        }

                        DebugLog(task.Result.GetRawJsonValue());
                    }
                    );
                }

                GUILayout.EndVertical();
        GUILayout.EndScrollView();
      }
    }

    void GUIDisplayLeaders() {
      GUI.skin.box.fontSize = 36;
      scrollPosition = GUILayout.BeginScrollView(scrollPosition, false, true);
      GUILayout.BeginVertical(GUI.skin.box);

      foreach (string item in leaderBoard) {
        GUILayout.Label(item, GUI.skin.box, GUILayout.ExpandWidth(true));
      }

      GUILayout.EndVertical();
      GUILayout.EndScrollView();
    }

    // Render the GUI:
    void OnGUI() {
      GUI.skin = fb_GUISkin;
      if (dependencyStatus != DependencyStatus.Available) {
        GUILayout.Label("One or more Firebase dependencies are not present.");
        GUILayout.Label("Current dependency status: " + dependencyStatus.ToString());
        return;
      }
      Rect logArea, controlArea, leaderBoardArea;

      if (Screen.width < Screen.height) {
        // Portrait mode
        controlArea = new Rect(0.0f, 0.0f, Screen.width, Screen.height * 0.5f);
        leaderBoardArea = new Rect(0, Screen.height * 0.5f, Screen.width * 0.5f, Screen.height * 0.5f);
        logArea = new Rect(Screen.width * 0.5f, Screen.height * 0.5f, Screen.width * 0.5f, Screen.height * 0.5f);
      } else {
        // Landscape mode
        controlArea = new Rect(0.0f, 0.0f, Screen.width * 0.5f, Screen.height);
        leaderBoardArea = new Rect(Screen.width * 0.5f, 0, Screen.width * 0.5f, Screen.height * 0.5f);
        logArea = new Rect(Screen.width * 0.5f, Screen.height * 0.5f, Screen.width * 0.5f, Screen.height * 0.5f);
      }

      GUILayout.BeginArea(leaderBoardArea);
      GUIDisplayLeaders();
      GUILayout.EndArea();

      GUILayout.BeginArea(logArea);
      GUIDisplayLog();
      GUILayout.EndArea();

      GUILayout.BeginArea(controlArea);
      GUIDisplayControls();
      GUILayout.EndArea();
    }
  }
}

[chkuang-g]

Hi @hamidhomatash

Three things about your use-case:

• Sorting functions like OrderByChild(), LimitToFirst() and StartAt() returns a Query object which contains the new constraints. That is, the original Query, or DatabaseReference for you case, will not be changed by calling OrderByChild(), LimitToFirst() or StartAt(). You need to use their return value to access ValueChanged or GetValueAsync()
• The string returned by GetRawJsonValue() will always be ordered by key as a JSON string, since it is a Json string. If you like to access it in the order you specified, use enumerator instead, ex. Datasnapshot.Children. This will enumerate children using your constraints.
• I would recommend ContinueWithOnMainThread over ContinueWith if you are planning to call Unity API in it.

Please try the following instead.

    // Get a Query object only returns the first 5 records by score.
    var top5 = Firebase.Database.FirebaseDatabase.DefaultInstance.GetReference("leaderboards/ai").OrderByChild("score").LimitToFirst(5);
    // Keep this query synced.
    top5.KeepSynced(true);
    top5.ValueChanged += (sender, args) =>
    {
        if (args.DatabaseError != null)
        {
            Debug.LogError(args.DatabaseError.Message);
            return;
        }

        // Iterate children in order based on "score"
        if (args.Snapshot != null) {
          foreach (var record in args.Snapshot.Children) {
            DebugLog(record.GetRawJsonValue());
          }
        }
    };

    top5.GetValueAsync().ContinueWithOnMainThread(task =>
    {
        if (task.Exception != null)
        {
            throw task.Exception;
        }
        if (!task.IsCompleted)
        {
            return;
        }

        // Iterate children in order based on "score"
        if (task.Result != null) {
          foreach (var record in task.Result.Children) {
            DebugLog(record.GetRawJsonValue());
          }
        }
    });

Let us know if this resolves your issue.

[hamidhomatash]

Hi @chkuang-g

Thank you for your help this solves the issues. I now see it was an error on my part since the call to GetValueAsync() or the event added to ValueChanged was being done on the DatabaseReference rather than the Query object being returned. I guess I was expecting the calls to alter the original DatabaseReference rather than return a new Query object that has the ordering and limits.

Thanks for the information about GetRawJSONValue() and ContinueWithOnMainThread

Again appreciate the help thank you

[chkuang-g]

FYI, DatabaseReference is derived fromQuery :)