1

I'm implementing a chat using Firebase Realtime DB. There's a requirement, that some chatrooms can show new messages by default, but messages in different chatrooms have to be approved first before displaying them.

The DB structure in Firebase is nested like this:

/chat/$roomID/$messageID/

and each message object is structured as follows:

{
    user: string
    content: string
    isApproved: boolean
    clientStamp: number // client-side unix timestamp
    serverStamp: number // server-side unix timestamp, via `firebase.database.ServerValue.TIMESTAMP`
}

Having only the display-by-default chatrooms would be easy - I'd order the data by serverStamp:

let query = DBConn.ref('chat/' + roomID).orderByChild('serverStamp').limitToLast(50);
query.on("value", snapshot => {
     // set the list of messages to display
     this.appendSnapshot(snapshot);
});

The approve-first rule makes this approach difficult however. The obvious solution would be to show only messages with attribute isApproved set to true. But if I'd query for the last N messages ordered by timestamp, and someone would post N unapproved messages, then that would oust any of the approved messages from the query result and the chat would be empty. Realtime DB has the following limitation, which makes it impossible to query by both timestamp + isApproved:

You can only use one order-by method at a time

So right now I'm workarounding it the following way:

  • messages are ordered by clientStamp (clientside unix timestamp) instead of serverStamp
  • when sending a new message to server, I divide the value of clientStamp by two, which mean these new messages will never oust the already approved older messages from the query result (since their timestamp has higher value)
  • when a message gets approved, the value of clientStamp is set to a value of firebase.database.ServerValue.TIMESTAMP (serverside time), which pushes it to the top of the chat query (orderByChild('serverStamp'))

... but this is rather a quick workaround. Is there any way how to make this work nicely for both display-by-default and approve-first chatrooms?

Frank van Puffelen
  • 565,676
  • 79
  • 828
  • 807
yedpodtrzitko
  • 9,035
  • 2
  • 40
  • 42

1 Answers1

1

I like your approach with setting the timestamp as part of an approval flow.

The more common solution is the one I documented in Query based on multiple where clauses in Firebase

Here that'd mean you have a property isApproved_serverStamp where you store the combined values of the isApproved and serverStamp properties, so like true_1632065222747. You can then query for the most recent approved messages with:

DBConn.ref('chat/' + roomID).orderByChild('isApproved_serverStamp').startAt("true_").limitToLast(50)
Frank van Puffelen
  • 565,676
  • 79
  • 828
  • 807
  • hm... using `.startAt('true_')` sure makes it better, plus that column can be guarded via Rules, unlike `clientStamp`. I guess I'll take it, thanks. Do you think the index on given column is less efficient since all the values now basically start with `true_`? – yedpodtrzitko Sep 19 '21 at 15:45
  • Index efficiency is outside of your control, but the database server typically handles such indexed quite well for hundreds of thousands of nodes. You'll inedeed need to implement [query based security rules](https://firebase.google.com/docs/firestore/security/rules-query) to ensure users can't request unapproved messages – Frank van Puffelen Sep 19 '21 at 15:56
  • Thanks, I appreciate the thoroughness of the comment. I sure take the Security Rules into account (that's what I was referring to by `Rules` in my previous comment). – yedpodtrzitko Sep 19 '21 at 16:11