Firebase Query Methods Startat() Taking Case Sensitive Parameters

firebase query methods startAt() taking case sensitive parameters

There is currently no support for lowercase searches in Firebase. The best way to handle this would be store the lowercase string along side the original string and then query the lowercase string instead.

var ref_restMenu = firebase.database().ref()
    .child('Restaurants')
    .child('Company')
    .child('menu');
var item = "Apple Pie";
// Or however you store data
ref.push({
    itemName: item,
    itemNameLower: item.toLowerCase(),
    ...
})

Then you could query as so:

//Check if item is already exist!
// query itemNameLoweruse and .toLowerCase()
ref_restMenu.orderByChild("itemNameLower").startAt(itemName.toLowerCase()).once("value", function(snapshot) {
    var data = snapshot.val(); 
    if(data !== null) {
        //We will ger item name and restaurant id from this data.
        callback(data);
    } else {
        //Item not found in globalMenu
        console.log("%c Item not found in Global Menu", "color: red");
    }
});

This does require replicating the data, but as of now there is not an easier foreseeable option.

Reference: Firebase Google Forum

Firebase and querying for a term in Swift

Since you're using queryEqualToValue, you will only get results where the userComment matches exactly with the value you specified.

If you want results where userComments starts with the value, you should use a combination of queryStartingAtValue and queryEndingAtValue:

DataService.dataService.BASE_REF.child("Posts").
    child(selectedComment.commentKey).child("comments").
    queryOrderedByChild("userComment")
    .queryStartingAtValue(comment). queryEndingAtValue(comment+"\uF8FF")
    observeSingleEventOfType(.Value, withBlock: { (snapshot) in

This gives you a range of results, from starting with comment to those starting with comment followed by the last unicode character.

But this still won't match uppercase and lowercase results. If you want that, you will have to store the data in a uniform case to enable searching. The Firebase Database is more focused on realtime data synchronization than on completeness of its (text) search capabilities.

Also see:

• Case insensitive sorting with Firebase orderByChild
• firebase query methods startAt() taking case sensitive parameters
• how to filter android listview items with firebase query?

How to search Firebase text by lower case also?

There are two ways in which you can solve this problem. The first one would be to store the name of your items in your database in lowercase even from the beginning by using the method toLowerCase(). So if you want to search the names by lower case will work perfectly fine. Then if you want to dispay the names in your activity and you want to capitalize the first letter, then just use the following line of code:

String firstLetterCapital = lowerCaseItemName.substring(0, 1).toUpperCase() + lowerCaseItemName.substring(1);

So if you you have an item that has the name of item, using the above code, the result will be: Item.

And the second approach would be to store the lowercase name of your items as a property of your item like this:

Firebase-root
    |
    --- items
          |
          --- ItemIdOne
                |
                --- itemName: Item
                |
                --- itemToLowerCase: item

Then to create the search you can use a query that looks like this:

DatabaseReference rootRef = FirebaseDatabase.getInstance().getReference();
DatabaseReference itemsRef = rootRef.child("items");
Query query = itemsRef.orderByChild("itemToLowerCase").startAt(itemName.toLowerCase());

Making a firebase query search NOT case sensitive

When using Firebase Database, that is the much you get to from orderByChild() function. It returns data either ascending or descending order and you cannot customize your result with extended queries.

However, You can try a something else that may be a bit more expensive.
Get all the children you want as an array, saving Child2 and its key. You can then change the string to upper case or lower case and access the desired result using the key.

The reference result would be

FirebaseDatabase.getInstance().getReference("Child1").child(key);

EDIT

To add on your logic, If you want "Dave" or "dave" to return Dave and dave
You can edit your query to startAt(text.toUppercase) and endAt(text.toLowerCase+ "\uf8ff"). This will return DAVE, Dave, dave etc

Cloud Firestore Case Insensitive Sorting Using Query

Sorting and filtering in Cloud Firestore are case sensitive. There is no flag to make the sorting or filtering ignore the case.

The only way to achieve your use-case is to store the field twice.

Let's say your field that stores 'AAA' & 'aaa' is called myData. In your client code you'll need to store a second field called myData_insensitive where you store a case-insensitive copy of the data.

DocA:
-> myData = 'AAA'
-> myData_insensitive = 'AAA'

DocB:
-> myData = 'aaa'
-> myData_insensitive = 'AAA'

DocC:
-> myData = 'BBB'
-> myData_insensitive = 'BBB'

DocD:
-> myData = 'bbb'
-> myData_insensitive = 'BBB'

Now you can query and/or order by myData_insensitive, but display myData.

Two interesting thing about this area is:

1. With Unicode, removing case is more complex than just 'toLowerCase'
2. Different human languages will sort the same characters differently

Without creating separate indexes for each collation to solve (2), one implementation approach to deal with (1) is via case folding. If you want to only support modern browser versions, then the following gives you a JavaScript example:

caseFoldNormalize = function (s){
  return s.normalize('NFKC').toLowerCase().toUpperCase().toLowerCase()
};
caseFoldDoc = function(doc, field_options) {
  // Case fold desired document fields
  if (field_options != null) {
    for (var field in field_options) {
      if (field_options.hasOwnProperty(field)) {
        switch(field_options[field]) {
          case 'case_fold':
            if (doc.hasOwnProperty(field) && Object.prototype.toString.call(doc[field]) === "[object String]") {
              doc[field.concat("_insensitive")] = caseFoldNormalize(doc[field])
            }
            break;
        }
      }
    }
  }
  return doc;
}

var raw_document = {
  name: "Los Angeles",
  state: "CA",
  country: "USA",
  structure: 'Waſſerſchloß',
  message: 'quıt quit' // Notice the different i's
};

var field_options = {
  name: 'case_fold',
  country: 'case_fold',
  structure: 'case_fold',
  message: 'case_fold'
}

var firestore_document = caseFoldDoc(raw_document, field_options);

db.collection("cities").doc("LA").set(firestore_document).then(function() {
  console.log("Document successfully written!");
}).catch(function(error) {
  console.error("Error writing document: ", error);
});

This will give you a document in Cloud Firestore with the following fields:

{ 
 "name": "Los Angeles", 
 "state": "CA", 
 "country": "USA", 
 "structure": "Waſſerſchloß", 
 "message": "quıt quit", 
 "name_casefold": "los angeles", 
 "country_casefold": "usa", 
 "structure_casefold": "wasserschloss", 
 "message_casefold": "quit quit"
}

To handle older browser, you can see one solution in How do I make toLowerCase() and toUpperCase() consistent across browsers

---

Related Topics