how to count childata on firebase with match childata

Question

im using firebase database on my test project what i wanted to do is something similar to how this query on mysql works..

select COUNT(*) as title, date from users group by date

this is how my firebase db looks like..

users
  |--askdoawih123hi1h2i
      |--name:"john"
      |--last:"kill",
      |--date:"2018-01-06 18:00"
  |--askdo7a87sd56as1h2i
      |--name:"Doe",
      |--last:"kill",
      |--date:"2018-01-06 18:00"
  |--askdoawa78sd7a81h2i
      |--name:"Mike",
      |--last:"kill",
      |--date:"2018-01-07 18:00"
  |--askd8a9sd8as9a89si1h2i
      |--name:"Ron",
      |--last:"kill",
      |--date:"2018-01-08 06:00"
  |--askdoaa9sda98a3hi1h2i
      |--name:"mich",
      |--last:"kill",
      |--date:"2018-01-09 06:00"

and the result that i wanted to achieve was something like this.

[{title:"2", star:"2018-01-06", title:"1", start:"2018-01-07", title:"1", start:"2018-01-08",title:"1", start:"2018-01-09",}]

and this is my script..

ref.once('value', function (snapshot) {
    var promises = [];
    var childData = snapshot.numChildren();

     snapshot.forEach(function (childSnapshot) {
        startTime = childSnapshot.val().startDateTime;

        promises.push({
            title: childData,
            start: startTime
        });
    });
     res.json(promises);
});

the result of this query is like this..

[{"title": 5,"start": "2018-01-06 18:00"},{"title": 5,"start": "2018-01-06 18:00"},{"title": 5,"start": "2018-01-07 18:00"},{"title": 5,"start": "2018-01-08 06:00"},{"title": 5, "start": "2018-01-09 06:00"}]

is there a way to achieve how the query of mysql works in firebase?

score 1 · Answer 1 · edited Jan 19 '18 at 15:20

You may slightly change your script in order to get a result as you needed:

   firebase.database().ref('users').orderByChild('date').once('value', snap={
        snap.forEach( c => {        
            if ( d==(c.val().date).substr(0,10) ) {
                   t+=1
               } else if (d=="") {
                    d = c.val().date.substr(0,10); 
               } else {                                                       
                    promises.push({"title": t+"", "start" : d }); // t +"": to convert String value
                    d = c.val().date.substr(0,10);
                    t = 1;    
            } 
        })
    console.log(promises) // [{title:"2", start:"2018-01-06"}, {title:"1", start:"2018-01-07"}, {title:"1", start:"2018-01-08"},{title:"1", start:"2018-01-09",}]

Note: For performance reason, you will need index also date child at firebase console. An example is:

{
  "rules": {
    ".read": true,
    ".write": "auth != null",
    "users": {
      ".indexOn": ["date"]
    }
  }
}

score 0 · Answer 2 · answered Jan 19 '18 at 15:28

When you're using a NoSQL database, consider modeling/augmenting the data in your database to match what you want to show on the screen.

In this case, you have a use-case that shows the number of users for each data. Instead of having each user run the (great and upvoted) query that HakanC wrote, you could instead keep the exact list that you want to show in the database. So that would be:

{
  "2018-01-06": 2,
  "2018-01-07": 1,
  "2018-01-08": 1,
  "2018-01-09": 1,
}

Once you have this structure in the database, reading and showing it in each client becomes trivial. This is a common pattern when you use a NoSQL database: you change the data model, which makes the write operations more complex. But in return your read operations become trivial, which explains why NoSQL databases are known for scaling to so many readers.

The "only" problem you now have left is to make sure those counters are in your database. This you do by always doing multiple writes: when you add a user node, you also update the corresponding counter(s). The Cloud Firestore documentation has an example of such aggregation functions. While the approach is equivalent to what you need to do, the code isn't copy/paste applicable for you (since Cloud Firestore has a different API.

Also see these related questions:

how to count childata on firebase with match childata

2 Answers2