AngularFireDatabase - Sort an Object of Objects by the Sum of Values on a Child Object

Question

I have a Firebase Realtime Database that looks like this:

{
  "Posts": {
    "k4aaluyjh5" : {
      "likes": {
        "uid1" : 1,
        "uid2" : 1,
        "uid3" : -1,
        "uid4" : 1,
        "uid5" : 1,
        "uid6" : -1,
        "uid7" : 1,
        "uid8" : 1,
        "uid9" : 1,
        "uid10" : 0,
        "uid11" : 1,
        "uid12" : 1
      }
    },
    "949rAWacb1" : {
      "likes": {
        "uid1" : 1,
        "uid2" : 1,
        "uid3" : 1
        
      }
    },
    "57jrnbakh1":{
      "likes": {
        "uid1" : -1,
        "uid2" : 1
      }
    }
  }
}

As shown in the JSON above, the value of a user’s like can either be 1, 0, or -1. I then use the sum of the values under the likes object to get the total number of likes per post. I have used .indexOn on the security rules to optimize queries for likes as shown below.

{
  "rules": {
    "Posts":{
      ".indexOn": "likes"
    },
    ".read": "true",
    ".write": "true"
  }
}

However, AngularFireDatabase base does not seem to have an option for sorting the posts by the sum of likes per post. I can’t find a way to do it; the goal is to sort the posts with the most liked ones coming first. The following is my .TS code:

 getListbyTop() {
    this.postList= this.firebaseDb.list('Posts', query =>
      query.orderByChild('likes').limitToFirst(100)
    );
    return this.postList
  }

Answer 1

Firebase (and most NoSQL databases) has no support for aggregation in query. If you want to order on some value, that value has to be present in the database. So since you want to sort on the number of likes, you will have to store that value in the JSON and keep it up to date when writing likes.

For example, the JSON could look like this:

{
  "Posts": {
    "k4aaluyjh5" : {
      "sumOfLikes": 7,
      "likesCount": 12,
      "likes": {
        "uid1" : 1,
        "uid2" : 1,
        "uid3" : -1,
        "uid4" : 1,
        "uid5" : 1,
        "uid6" : -1,
        "uid7" : 1,
        "uid8" : 1,
        "uid9" : 1,
        "uid10" : 0,
        "uid11" : 1,
        "uid12" : 1
      }
    },
    "949rAWacb1" : {
      "sumOfLikes": 3,
      "likesCount": 3,
      "likes": {
        "uid1" : 1,
        "uid2" : 1,
        "uid3" : 1
        
      }
    },
    "57jrnbakh1":{
      "sumOfLikes": 0,
      "likesCount": 2,
      "likes": {
        "uid1" : -1,
        "uid2" : 1
      }
    }
  }
}

Now you can sort on either the sum of the lines/dislikes, or the total number of likes/dislikes cast, with:

query.orderByChild('sumOfLikes')