[firebase] Query based on multiple where clauses in Firebase

{
  "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?

This question is related to firebase firebase-realtime-database

The answer is


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

ref.orderByChild("lead").startAt("Jack Nicholson").endAt("Jack Nicholson").listner....

This will work.


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