0

I have three collections in my Firebase project, one contains locations that users have checked in from, and the other two are intended to hold leaderboards with the cities and suburbs with the most check ins.

However, as a bit of a newbie to NOSQL databases, I'm not quite sure how to do the queries I need to get and set the data I want.

Currently, my checkins collection has this structure:

{ Suburb:,
City:,
Leaderboard:}

The leaderboard entry is a boolean to mark if the check in has already been added to the leaderboard.

What I want to do is query for all results where leaderboard is false, count the entries for all cities, count the entries for all suburbs, then add the city and suburb data to a separate collection, then update the leaderboard boolean to indicate they've been counted.

exports.updateLeaderboard = functions.pubsub.schedule('30 * * * *').onRun(async context => {
    db.collection('Bears')
    .where('Leaderboard', '==', 'false')
    .get()
    .then(snap =>{
        snap.forEach(x => {
            //Count unique cities and return object SELECT cities,COUNT(*) AS `count` FROM Bears GROUP BY cities
        })
    })
    .then(() => {
        console.log({result: 'success'});
    })
    .catch(error => {
        console.error(error);
    });
})

Unfortunately, I've come to about the limit of my knowledge here and would love some help.

Frank van Puffelen
  • 565,676
  • 79
  • 828
  • 807
Hamish McBrearty
  • 159
  • 2
  • 9
  • Firestore doesn't have any aggregation queries like SQL, so you're in for a lot of manual counting, if you're not already maintaining that data as it changes over time. – Doug Stevenson Apr 08 '20 at 03:04
  • Thanks Doug. My intention here is to update the leaderboard once per hour to keep the counting down. This site is only likely to be up for a few weeks so I'm not too concerned about maintaining it for a long time. – Hamish McBrearty Apr 08 '20 at 03:44

2 Answers2

0

As clarified in this other post from the Community here, Firestore doesn't have a built-in API for counting documents via query. You will need to read the whole collection and load it to a variable and work with the data then, counting how many of them have False as values in their Leaderboard document. While doing this, you can start adding these cities and suburbs to arrays that after, will be written in the database, updating the other two collections.

The below sample code - untested - returns the values from the Database where the Leaderboard is null, increment a count and shows where you need to copy the value of the City and Suburb to the other collections. I basically changed some of the orders of your codes and changed the variables to generic ones, for better understanding, adding a comment of where to add the copy of values to other collections.

...
// Create a reference to the collection of checkin
let checkinRef = db.collection('cities');

// Create a query against the collection
let queryRef = checkinRef.where('Leaderboard', '==', false);

var count = 0;

queryRef.get().
    .then(snap =>{
        snap.forEach(x => {
           //add the cities and suburbs to their collections here and update the counter
           count++;
        })
    })
...

You are very close to the solution, just need now to copy the values from one collection to the others, once you have all of them that have False in leaderboard. You can get some good examples in copying documents from a Collection to another, in this other post from the Community: Cloud Functions: How to copy Firestore Collection to a new document?

Let me know if the information helped you!

gso_gabriel
  • 4,199
  • 1
  • 10
  • 22
  • If you use this approach, keep in mind that your fetch could consume all your memory, crashing your function. To be safe, you would have to open a cursor instead and page through the data. Then you would have to batch all your writes, where Firestore can do a maximum of 500 at a time. – windowsill Apr 08 '20 at 07:19
  • 1
    Thanks for the info @windowsill ! Considering that the OP informed that this will be used only for a couple of weeks and that he is not thinking about keeping it for a long time, I believe this method should be fine for him just now. – gso_gabriel Apr 08 '20 at 07:45
0

Firebase is meant to be a real-time platform, and most of your business logic is going to be expressed in Functions. Because the ability to query is so limited, lots of problems like this are usually solved with triggers and data denormalization.

For instance, if you want a count of all mentions of a city, then you have to maintain that count at event-time.

// On document create
await firestore()
  .collection("city-count")
  .doc(doc.city)
  .set({
    count: firebase.firestore.FieldValue.increment(1),
  }, { merge: true });

Since it's a serverless platform, it's built to run a lot of very small, very fast functions like this. Firebase is very bad at doing large computations -- you can quickly run in to mb/minute and doc/minute write limits.

Edit: Here is how Firebase solved this exact problem from the perspective of a SQL trained developer https://www.youtube.com/watch?v=vKqXSZLLnHA

windowsill
  • 3,599
  • 1
  • 9
  • 14