Limit to last message Firebase realtime database. React Native

Question

I would like to get the last message in my Firebase Realtime Database given the value of a child inside the message. Is this possible?

Here is my messages below in the realtime database:

I would like to obtain the last message which was sent with 'sentWhileBlocked' == false. Can this be achieved using the firebase realtime database query? What will be the query if so?

This is the query I have so far which prints the last message:

 db.ref('messages').child(childSnapshot.key).orderByKey().limitToLast(1).on('value', (dataSnapshots) => {
    if (dataSnapshots.val()) {
       console.log("val key: "+JSON.stringify(dataSnapshots.val()));
    }
 });

Would I need to do this with a for loop? If so, how would this look?

Thanks.

Answer 1

In comparison to Cloud Firestore, the Firebase Realtime Database has very limited built-in query functionality. One of these limitations is that RTDB queries can only be performed on a single child value - there is no name1=value1 AND name2=value2 query functionality.

From here, you have two options if you chose to stick with the RTDB as your database:

Option 1) Rely on the order of the message keys

When using orderByChild to sort data in a query, duplicates are sorted lexicographically by key.

This means your query can be as simple as:

const query = db.ref('messages')
  .child(childSnapshot.key)
  .orderByChild("sentWhileBlocked")
  .equalTo(false)
  .limitToLast(1);

query
  .then((querySnapshot) => {
    if (!querySnapshot.hasChildren()) {
      // handle no sentWhileBlocked=false values
      console.error(`No matching messages found.`);
    } else {
      let messageSnapshot;
      querySnapshot.forEach(s => messageSnapshot = s);
      // do something with messageSnapshot
      console.log(`Matching Message ID: ${messageSnapshot.key}`);
    }
  })
  .catch((err) => { ... })

Option 2) Denormalize your data to create a composite index

While you can rely on the order of keys to sort your data, as you store a timestamp value, you can use that value to sort the data instead. This may be important if you update the timestamp value when a user edits their message. While you could loop through your structure as you came up with to find the latest timestamp with a sentWhileBlocked value of false, this is inefficient at scale as you could end up downloading a whole bunch of unnecessary data. What you will need to do is "denormalize" your data by creating an index that merges both your timestamp and sentWhileBlocked fields. This Firebase video covers it as query #8.

When merging the two fields, you may be tempted to toggle a binary bit or throw a 1 or 0 at the start of the timestamp to store the sentWhileBlocked value. This introduces an issue where some clients may treat this as a number larger than what a 32bit signed integer can handle which could lead to overflow problems.

To force Firebase to not treat it as numeric, concatenate the values with a symbol that doesn't occur in a valid number:

{
  sentWhileBlocked: false,
  timestamp: 1634376119733,
  sentWhileBlocked_timestamp: "0_1634376119733", // or "false_1634376119733"
  // ...
}

In its current form this introduces a new problem - this value is now lexicographically sorted because it is now a string, not a number. As an example, if you had the array ["1","2","3","10","11"] and you sort it, you end up with ["1","10","11","2","3"]. To prevent this causing problems, we should make sure to pad this number with at least 1 zero so that when the timestamp passes 10000000000 (in 2086), your timestamps don't screw up.

This gives a new structure of:

{
  sentWhileBlocked: false,
  timestamp: 1634376119733,
  sentWhileBlocked_timestamp: "0_01634376119733",
  // ...
}

When writing that data to the database, you can merge the values and pad the number value of timestamp as needed using:

const newMessageData = {
  sentWhileBlocked: false,
  timestamp: 1634376119733,
  // ...
}

// before writing to RTDB
newMessageData.sentWhileBlocked_timestamp = (messageData.sentWhileBlocked ? "1" : "0") + "_" + messageData.timestamp.toString().padStart(14,"0");

// write newMessageData to database
db.ref("messages")
  .child(chatroomId)
  .push(newMessageData)
  .then(() => console.log("Uploaded message"))
  .catch((err) => console.error("Failed to upload message", err))

This allows you to use this query to fetch the latest message with sentWhileBlocked set to false:

const query = db.ref('messages')
  .child(childSnapshot.key)
  .orderByChild("sentWhileBlocked_timestamp")
  .endsWith("0_\uf8ff") // use .startsWith("1_") for `sentWhileBlocked` = `true`
  .limitToLast(1);

query
  .then((querySnapshot) => {
    if (!querySnapshot.hasChildren()) {
      // handle no sentWhileBlocked=false values
      console.error(`No matching messages found.`);
    } else {
      let messageSnapshot;
      querySnapshot.forEach(s => messageSnapshot = s);
      // do something with messageSnapshot
      console.log(`Matching Message ID: ${messageSnapshot.key}`);
    }
  })
  .catch((err) => { ... })

Note: If using this method, you should add this field to your ".indexOn" list in your Firebase RTDB Security Rules for the best performance.