Query based on multiple where clauses in Firebase

Question

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.

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

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

    }
});

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.

{
  "movies": {
    "movie1": {
      "genre": "comedy",
      "name": "As good as it gets",
      "lead": "Jack Nicholson",
      "year": 2014,
      "filters": [
        "comedy_Jack Nicholson",
        "comedy_2014"
      ]
    }
  }
}

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<Movie> movies = new ArrayList<>();
                        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<Movie> list,  final String genre) {
        List<Movie> movies = new ArrayList<>();
        movies = list.stream().filter(o -> o.getLead().equals(genre)).collect(Collectors.toList());
        System.out.println(movies);

        employees.forEach(movie -> System.out.println(movie.getFirstName()));
    }