Categories
firebase firebase-realtime-database

Query based on multiple where clauses in Firebase

292

{
  "movies": {
    "movie1": {
      "genre": "comedy",
      "name": "As good as it gets",
      "lead": "Jack Nicholson"
    },
    "movie2": {
      "genre": "Horror",
      "name": "The Shining",
      "lead": "Jack Nicholson"
    },
    "movie3": {
      "genre": "comedy",
      "name": "The Mask",
      "lead": "Jim Carrey"
    }
  }
}

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?

0

    283

    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.

    "movie1": {
        "genre": "comedy",
        "name": "As good as it gets",
        "lead": "Jack Nicholson",
        "genre_lead": "comedy_Jack Nicholson"
    }, //...
    

    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:

    "movie1": {
        "genre": "comedy",
        "name": "As good as it gets",
        "lead": "Jack Nicholson",
        "genre_year": "comedy_1997"
    }, //...
    

    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. "comedy_1997-12-25". 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:

      "movies"
          // the same structure you have today
      "by_genre"
          "comedy"
              "by_lead"
                  "Jack Nicholson"
                      "movie1"
                  "Jim Carrey"
                      "movie3"
          "Horror"
              "by_lead"
                  "Jack Nicholson"
                      "movie2"
          
    

    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.

    17

    • 25

      Is this still relevant in 2016 with Firebase V3? Aren’t there better ways to do this?

      – Pier

      Jun 3, 2016 at 18:21

    • 5

      I’ve written a personal library to help wrap the #2 solution into an easy to use API. The library is called Querybase and it’s available for JS users: github.com/davideast/Querybasehttps://github.com/davideast/…

      Jun 6, 2016 at 13:33

    • 33

      Why we can’t use .equalTo('comedy') instead of .startAt('comedy').endAt('comedy')?

      – JCarlosR

      Nov 1, 2016 at 14:33

    • 49

      It is 2018, is there no simple solution for this? This is like query 101 in relational database. And given all the comments on David’s video talking about SQL #8, I would’ve expected Google to fix it by now. Did I miss some update?

      – Snake

      Feb 24, 2018 at 21:47

    • 15

      @Snake Feb 2019 and the problem still isn’t fixed…. Google are too slow.

      Feb 14, 2019 at 10:17

    57

    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 => console.log(snap));
    

    21

    • 1

      Any TypeScript definitions available for your library?

      Jun 19, 2016 at 20:29

    • 2

      It’s written in TS! So just import 🙂

      Jun 19, 2016 at 20:30

    • 10

      do you know why they made the database so restrictive ?

      – Ced

      Aug 21, 2017 at 21:38

    • 2

      Any option to do an IOS Swift/Android version

      – mding5692

      Sep 12, 2017 at 19:46

    • 2

      David, any android/Java equivalence to it?

      – Snake

      Feb 24, 2018 at 21:48

    7

    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) {
    
        }
    });
    

    4

    • 4

      There is no getLead() method on DataSnapshot object.

      Jul 31, 2016 at 13:59

    • 5

      He converted it to Movie object and assuming there is a getLead() metho.

      Dec 14, 2017 at 22:20

    • 2

      Thankz a lot. It’s working such as more where clauses

      Jul 18, 2018 at 6:16

    • 1

      I am using firestore. How can we use where conditions in this situation – fetch all the chat conversations where “(senderid == loggedinuserid and receiverid == 10) or (senderid == 10 and receiverid == loggedinuserid)”? kindly suggest. Thanks.

      – Kamlesh

      Jun 15, 2021 at 20:01