Firebase - How to get all posts where someone commented in the last x days

Question

I'm trying to receive a list of posts where someone posted a comment in the last 2 days from the Firebase realtime database.

const currentTime = Date.now();
const twoDaysAgo = currentTime - (2 * 24 * 60 * 60 * 1000);

Database.ref(`posts`).orderByChild(`comments/{comment}/createdAt`).endAt(twoDaysAgo).on('child_added', snapshot => {
    console.log(snapshot.val());
});

I think I'm doing something wrong in the orderByChild part because I'm receiving all the posts.

My database structure looks like this

Answer 1

Queries in the Firebase Realtime Database can only order/filter for a value that is at a known path under each child. Your .orderByChild(`comments/{comment}/createdAt`) does not work. For more on this, see Firebase Query Double Nested and Firebase query if child of child contains a value.

As usual when working with NoSQL databases, the solution is to change your data model to allow the use-case. In this case, you can add a lastCommentAt property to each post, and update that whenever you write a comment to a post. With that new property in place, the query becomes:

Database.ref(`posts`).orderByChild(`lastCommentAt`).endAt(twoDaysAgo)...

---

On a separate note: your nesting of the comments under the posts themselves violates some of the recommendations that the Firebase documentation makes about data modeling. Specifically, you should avoid nesting data and flatten data structures to ensure that the data scales.