0

I have set up my data in my firebase realtime database as follows:

Key{ Creation date, Popularity, Rating, Author}

Would it be possible to retrieve the answers to the following questions:

  1. what where the top 50 games last month in terms of popularity?
  2. what where the lowest ranked games this week?
  3. what is the highest rated game today?

Answering one would answer the other one probably, but just to be sure I put them all.

Frank van Puffelen
  • 565,676
  • 79
  • 828
  • 807
Erik Sombroek
  • 145
  • 1
  • 7
  • Hi you should refer this link it will give you an answer. https://firebase.google.com/docs/firestore/solutions/aggregation – Garvit Khamesra Dec 25 '18 at 15:56
  • But this is Firestore and not realtime database right? – Erik Sombroek Dec 25 '18 at 16:01
  • No, it is realtime. – Garvit Khamesra Dec 25 '18 at 16:06
  • So after having a look, I still don't see how I can combine queries with multiple keys over Firestore. I see the ratings being retreived but not within a certain time span. Additionally I would prefer an answer for real time database and not Firestore. ( 2 different products in firebase) – Erik Sombroek Dec 25 '18 at 16:22
  • After writing my answer below, I realize that you may be looking for aggregation queries, so queries where the database determines (for example) the average of all ratings. This are not built into the Firebase (neither into the Realtime nor into Firestore). You will either have to read all data for the interval and calculate client-side, or have to store the aggregate values in the database and update them on each write. – Frank van Puffelen Dec 25 '18 at 17:08

1 Answers1

2

All of these require that you consider multiple properties. And since the Firebase Realtime Database can only order/filter over a single property, you can't perform these without modifying the data model. For a good primer, read my answer here: Query based on multiple where clauses in Firebase

In this case, you'd need three extra properties for each node:

  1. a property combining the month and popularity, for your first query. For example: "month_popularity": "201812_125" (if the popularity is 125).
  2. a property combining the month and rating, for your second query. For example: "week_rating": "2018w51_4".
  3. a property combining the day and rating, for your third query. For example: "day_rating": "20181225_4".

With these properties, you can then order on the interval you want, and filter the values for the range you want. For example to get the top 50 games for this month:

ref.orderByChild("month_popularity").startAt("201812_").endAt("201812~")

Where the ~ is just a character after _ in ASCII, ensuring that we stop returning results after the ones from this month.

Frank van Puffelen
  • 565,676
  • 79
  • 828
  • 807
  • Thanks Frank! This really helps a lot. I do have the aggregation already in the DB, so this works fine for me. Fijne kerst ;) – Erik Sombroek Dec 25 '18 at 17:20
  • 1
    Am I correct to think that if I want this to work I need pre prepend 0's to my numbers? E.g. 201812_20 would be sorted after a 2018_100? Or does this not work like that? I could for instance take a cap at a billion and use 201812_0000000020 and 201812_000000100 – Erik Sombroek Dec 26 '18 at 15:06
  • Ah, yep good point. I forgot about that one. You'll indeed need to pad the numbers with `0`s to whatever is the maximum length that you need for your case. – Frank van Puffelen Dec 26 '18 at 20:14
  • Thanks! It's looking good so far. The more I use firebase, the better it gets. Love it. – Erik Sombroek Dec 26 '18 at 20:26