Firebase Query on Date Range on basis of Child Node

Question

I'm trying to generate a weekly report from some data in Firebase database and using Firebase cloud function for that purpose. Timestamp actually exists in child nodes. However, I'm unable to fetch any data.

My DB structure is as follows :

 {
   "threads" : {
      "228440-1704-4bba-87d7-27327" : {
         "messages" : {
           "8c98a76c-4456-4326-8e18-6d036b40bfa4" : {
              "time" : 1519997400,
              "payload": "someData"
            }, 
            "898376c-4456-4326-8e18-6deb4wr0bfa4" : {
              "time" : 1517174415302,
              "payload": "someData"
             }
          },
      "details" : {
        "created_At": 1514134435602 
        }
    }

Fetch data on basis of detail node: I'm trying to fetch threads where threads/details/created_at exists in my date range (say last week).

Fetch data on basis of messages node: Apart from this, in another query, i want to fetch all messages (in different threads) for last week. I guess I'll have to fetch threads even in this scenario with some thing like:

//fetch threads where message.time exists in last week

I'm using Firebase cloud functions, and my code is as:

exports.generateReportFromThreads = functions.https.onRequest((req, res) => {

    var ref = admin.database().ref('threads');
    var startingTime = new Date('Mon, 25 Dec 1995 13:30:00 GMT').getUnixTime();
    var currentDate = new Date('Fri, 2 Mar 2018 13:30:00 GMT').getUnixTime();

    return ref.orderByChild('details/created_at').startAt(startingTime).endAt(currentDate).once("value", (threadSnapshot) => {

      var numberOfThreads = threadSnapshot.numChildren()
      var totalMessages = 0

       threadSnapshot.forEach((snapshot) => {

        var ref = snapshot.child('messages')
         var messagesPerThread = ref.numChildren()
         totalMessages = totalMessages + messagesPerThread


      res.send(`
          <!doctype html>
          <html>
              <body>
               <p>Total number of threads = ${numberOfThreads}</p>
               <p>Total messages = ${totalMessages}</p>
              </body>
          </html>`
      );
   });
});

I've applied indexes in my DB as:

it is because this `orderByChild('details/created_at').startAt(startingTime).endAt(currentDate)` does not exists — Peter Haddad, Mar 02 '18 at 14:39
@PeterHaddad, thanks for your response. it's just that i was testing and trying to fetch something someway. I'm not sure how to do this. Can you kindly guide? — Fayza Nawaz, Mar 02 '18 at 14:48

score 1 · Accepted Answer · answered Mar 02 '18 at 14:58

1

Firebase Database queries can only sort/filter on a property at a known path under each child.

Your structure is /threads/$threadid/messages/$messageid. You cannot run a query across all threads and then filter on the time of each messages. You will either have to load all messages and filter client-side/in your Cloud Function, or you will have to change your data structure to match your needs.

For more on this, see my answer here: Firebase Query Double Nested

answered Mar 02 '18 at 14:58

Frank van Puffelen

565,676
79
828
807

What about detail node? /threads/$threadid/detail/createdAt? @Frank van Puffelen – Fayza Nawaz Mar 02 '18 at 15:02
or, is there any way to perform pagination on Firebase DB using cloud functions?? – Fayza Nawaz Mar 02 '18 at 15:03
You can indeed order each thread on its creation data, since that value is at a fixed path under each child node. Note there is nothing specific about Cloud Functions here. You're using the regular Firebase Admin SDK for accessing the database and are bound to the exact same abilities and limits as other users of that SDK, and mostly to the same behavior as developers using the Web SDK. – Frank van Puffelen Mar 02 '18 at 15:18
makes sense. But still, I'm afraid, I'm not sure how to order each thread on basis of creation date? means, how will I do it in code? I for sure am doing something wrong in the query I have mentioned in my question. How to fix it? – Fayza Nawaz Mar 02 '18 at 17:38
That query looks fine to me. What behavior do you see? What behavior did you expect to see? It's often easiest to describe this by adding some logging, showing what it outputs, and telling what you expected. – Frank van Puffelen Mar 02 '18 at 17:55
provided that the date range is from 1995-2018, i expect it to return all the threads. However, it returns nothing. threadSnapshot.numChildren() is zero in this case. – Fayza Nawaz Mar 02 '18 at 18:22
Log the values of `startingTime` and `currentDate` and compare the to the values in your database. If they are what you expect them to be, replace the values in your code with hard-coded values and see if you can still reproduce it. I.e. note that `getUnixTime()` doesn't exist on the Node and JavaScript consoles I just tested on, so I'm immediately suspicious of whether that does what you expect it to do. – Frank van Puffelen Mar 02 '18 at 18:41
Thanks Frank. I'll check it out. getUnixTime() is the utility method i've written.. I'll check this thing and will get back. – Fayza Nawaz Mar 02 '18 at 18:55

Firebase Query on Date Range on basis of Child Node

1 Answers1