Query based on multiple where clauses in Firebase

Question

quot movies quot          quot movie1 quot            quot genre quot    quot comedy quot          quot name quot    quot As good as it gets quot          quot lead quot    quot Jack Nicholson quot              quot movie2 quot            quot genre quot    quot Horror quot          quot name quot    quot The Shining quot          quot lead quot    quot Jack Nicholson quot              quot movie3 quot            quot genre quot    quot comedy quot          quot name quot    quot The Mask quot          quot lead quot    quot Jim Carrey quot               I am a Firebase newbie  How can I retrieve a result from the data above where genre    comedy  AND lead    Jack Nicholson   What options do I have

User · Answer

ref orderByChild  lead   startAt  Jack Nicholson   endAt  Jack Nicholson   listner       This will work

User · Answer

Using Firebase s Query API  you might be tempted to try this         THIS WILL NOT WORK     ref    orderBy  genre      startAt  comedy   endAt  comedy      orderBy  lead                           THIS LINE WILL RAISE AN ERROR        startAt  Jack Nicholson   endAt  Jack Nicholson      on  value   function snapshot           console log snapshot val             But as  RobDiMarco from Firebase says in the comments   multiple orderBy   calls will throw an error  So my code above will not work  I know of three approaches that will work  1  filter most on the server  do the rest on the client What you can do is execute one orderBy   startAt    endAt   on the server  pull down the remaining data and filter that in JavaScript code on your client  ref    orderBy  genre      equalTo  comedy      on  child added   function snapshot           var movie   snapshot val          if  movie lead     Jack Nicholson               console log movie                  2  add a property that combines the values that you want to filter on If that isn t good enough  you should consider modifying expanding your data to allow your use-case  For example  you could stuff genre lead into a single property that you just use for this filter   quot movie1 quot          quot genre quot    quot comedy quot        quot name quot    quot As good as it gets quot        quot lead quot    quot Jack Nicholson quot        quot genre lead quot    quot comedy Jack Nicholson quot            You re essentially building your own multi-column index that way and can query it with  ref    orderBy  genre lead      equalTo  comedy Jack Nicholson      on  child added   function snapshot           var movie   snapshot val          console log movie          David East has written a library called QueryBase that helps with generating such properties  You could even do relative range queries  let s say that you want to allow querying movies by category and year  You d use this data structure   quot movie1 quot          quot genre quot    quot comedy quot        quot name quot    quot As good as it gets quot        quot lead quot    quot Jack Nicholson quot        quot genre year quot    quot comedy 1997 quot            And then query for comedies of the 90s with  ref    orderBy  genre year      startAt  comedy 1990      endAt  comedy 2000      on  child added   function snapshot           var movie   snapshot val          console log movie          If you need to filter on more than just the year  make sure to add the other date parts in descending order  e g   quot comedy 1997-12-25 quot   This way the lexicographical ordering that Firebase does on string values will be the same as the chronological ordering  This combining of values in a property can work with more than two values  but you can only do a range filter on the last value in the composite property  A very special variant of this is implemented by the GeoFire library for Firebase  This library combines the latitude and longitude of a location into a so-called Geohash  which can then be used to do realtime range queries on Firebase  3  create a custom index programmatically Yet another alternative is to do what we ve all done before this new Query API was added  create an index in a different node     quot movies quot           the same structure you have today    quot by genre quot         quot comedy quot             quot by lead quot                 quot Jack Nicholson quot                     quot movie1 quot                 quot Jim Carrey quot                     quot movie3 quot         quot Horror quot             quot by lead quot                 quot Jack Nicholson quot                     quot movie2 quot          There are probably more approaches  For example  this answer highlights an alternative tree-shaped custom index  https   stackoverflow com a 34105063  If none of these options work for you  but you still want to store your data in Firebase  you can also consider using its Cloud Firestore database  Cloud Firestore can handle multiple equality filters in a single query  but only one range filter  Under the hood it essentially uses the same query model  but it s like it auto-generates the composite properties for you  See Firestore s documentation on compound queries

User · Answer

Firebase doesn t allow querying with multiple conditions   However  I did find a way around for this   We need to download the initial filtered data from the database and store it in an array list                   Query query   databaseReference orderByChild  genre   equalTo  comedy                    databaseReference addValueEventListener new ValueEventListener                          Override                     public void onDataChange  NonNull DataSnapshot dataSnapshot                             ArrayList lt Movie gt  movies   new ArrayList lt  gt                             for  DataSnapshot dataSnapshot1   dataSnapshot getChildren                                  String lead   dataSnapshot1 child  lead   getValue String class                               String genre   dataSnapshot1 child  genre   getValue String class                                movie   new Movie lead  genre                                movies add movie                                                       filterResults movies   Jack Nicholson                                                                            Override                     public void onCancelled  NonNull DatabaseError databaseError                                                 Once we obtain the initial filtered data from the database  we need to do further filter in our backend   public void filterResults final List lt Movie gt  list   final String genre            List lt Movie gt  movies   new ArrayList lt  gt             movies   list stream   filter o - gt  o getLead   equals genre   collect Collectors toList             System out println movies            employees forEach movie - gt  System out println movie getFirstName

User · Answer

var ref   new Firebase  https   your firebaseio com      Query query   ref orderByChild  genre   equalTo  comedy    query addValueEventListener new ValueEventListener          Override     public void onDataChange DataSnapshot dataSnapshot            for  DataSnapshot movieSnapshot   dataSnapshot getChildren                  Movie movie   dataSnapshot getValue Movie class               if  movie getLead   equals  Jack Nicholson                      console log movieSnapshot getKey                                         Override     public void onCancelled FirebaseError firebaseError

User · Answer

I ve written a personal library that allows you to order by multiple values  with all the ordering done on the server  Meet Querybase  Querybase takes in a Firebase Database Reference and an array of fields you wish to index on  When you create new records it will automatically handle the generation of keys that allow for multiple querying  The caveat is that it only supports straight equivalence  no less than or greater than   const databaseRef   firebase database   ref   child  people    const querybaseRef   querybase ref databaseRef    name    age    location         Automatically handles composite keys querybaseRef push      name   David     age  27    location   SF          Find records by multiple fields    returns a Firebase Database ref const queriedDbRef   querybaseRef   where      name   David      age  27          Listen for realtime updates queriedDbRef on  value   snap   gt  console log snap

User · Answer

Frank s answer is good but Firestore introduced array-contains recently that makes it easier to do AND queries  You can create a filters field to add you filters  You can add as many values as you need  For example to filter by comedy and Jack Nicholson you can add the value comedy Jack Nicholson but if you also you want to by comedy and 2014 you can add the value comedy 2014 without creating more fields       quot movies quot          quot movie1 quot            quot genre quot    quot comedy quot          quot name quot    quot As good as it gets quot          quot lead quot    quot Jack Nicholson quot          quot year quot   2014         quot filters quot              quot comedy Jack Nicholson quot            quot comedy 2014 quot

[firebase] Query based on multiple where clauses in Firebase

Examples related to firebase

Examples related to firebase-realtime-database