How to Query based on multiple where clauses using eq filters in Firebase #17

Learn how to Query based on multiple where clauses using eq filters in Firebase to perform simple and compound queries in Cloud Firestore. In this chapter, we will learn to query based on multiple where clauses in Firebase because you know Firebase provide exact match "==" with case sensitive. So we will solve this issue in the chapter in detail. We will use the Laravel PHP SDK for the firebase for all the queries stuff.

Now lets consider a simple example, you have 'Student' list and there are two fields 'firstname' and 'lastname' and you want to search in both fields at the same time. So the main use of any search is that, you insert one or two characters and the system should search the most matching one, but that not the case in Firebase. All Firebase developers are facing this issue, which is most basic necessity. So lets first understand, currently what is provided by Firebase. See the below example of Student List.





Now here you see, we have a Student named 'Tim Dane' has firstname field = "Tim" and lastnmae field = "Dane". Now you have to search this student from whole list, so you start time 'Tim', so atleast it should be match one matching word. So do Firebase return "Tim Dane" on search of "Tim" or "Dane". Let's check.



This part is covered in Chapter 16 . You will get a clear idea.

Now assuming you have read the above chapter. So, in firebase you can write multiple where, like below



 $student = app('firebase.firestore')->database()->collection('Students');  


 $keyword1 = 'Tim';  
 $keyword2 = 'Dane';  
 $student = $student ->where('firstname','==', $keyword1)->where('lastname','==', $keyword2);  
So this above query will give you the result "Tim Dane". So we can multiple where but we require something like orWhere which Firebase does not support. Because you will have one Keyword and you have to search in firstname and lastname at a time. Something like below



 $keyword = 'Tim';  
 $student = $student ->where('firstname','==', $keyword)->orWhere('lastname','==', $keyword);  


But, above is not supported by Firebase. So to overcome, we have follow below steps.



So what can you do you store the two field values in one new field separated by a underscore "_". like below







Here, we created a new field "name" and stored the firstname "Tim" and lastname "Dane" with a underscore "Tim_Dane". And your laravel firebase query will look like below



 $student = app('firebase.firestore')->database()->collection('Students');  


 $keyword = 'Tim';  
 $student = $student ->orderBy('name','asc')  
           ->startAt([$keyword])  
           ->endAt([$keyword."\uf8ff"]);  




Here we used Orderby field 'name' in ascending order and used the startAt and endAt function of Firebase query. So this query orders all data by name in ascending starting at and ending at keyword 'Tim'. Now the main hack is the appended string "\uf8ff". This unicode is most last private unicode. Read more on google for this. So by this we would get all data containing 'Tim' keywords. You can also get results for keyword 'Dane'.



Now, this was example of two fields "firstname" and "lastname". So what we have more than two fields. So you can go on appending third field value in the "name" field by another underscore. Example: "middlename". so you can store "firstname_lastname_middlename" in "name" field.



So hope you have got par the very necessity functionality of every system that Firebase was not supporting. And now you can implement search in your all list pages. And promised in previous chapter, we have completed the point "2) Multiple field search" challenge. Now we still remain with "Case sensitive" issue. We will cover this in next chapter. Hope you are loving reading our chapters and let all know your doubts, feedback in the comment section below.

Post a Comment

9 Comments

  1. Replies
    1. So we will solve this issue

      This article is a great article that I have seen in my programming career so far, it helps me a lot to solve exact match "=" issue in Firebase with use of multiple where clauses, and will continue to do so in the future.

      website development company in Surat Gujarat

      Delete
    2. Thanks Tecocraft.

      Please follow us and share. It helps us.

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. What happen if i search by Dane instead of Tim?

    ReplyDelete
    Replies
    1. One extra field should store value as Tim_Dane, so search can find Dane.

      If you have to implement full text search, then you can use Algolia.

      Delete
  4. $query = $this->firestore->database()->collection('Subscriptions');

    if(isset($_GET['operator_name']) && $_GET['operator_name'] != '') {
    $query->where('operatorName', '==', $_GET['operator_name']);
    }

    if(isset($_GET['fee']) && $_GET['fee'] != '') {
    $query->orWwhere('feePerRole', '==', $_GET['operator_name']);
    }

    if(isset($_GET['start_date']) && $_GET['start_date'] != '') {
    $startDate = Carbon::parse($_GET['start_date'])->valueOf();
    $query->orWhere('startOn', '==', $startDate);
    }

    $subscriptions = $query->documents();


    Not working properly. Can you please help me about it. I a receiving the data in if but not able to filter data from query.

    ReplyDelete
    Replies
    1. Hi LaravelGuru,
      As you are using date comparison, can you check and debug if timezones of firebase and laravel project are correct

      Delete
    2. I am storing with same time format so, time is not an issue. Even simple string search is also not working.

      Delete