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

3 Answers 11

up vote 141 down vote accepted

Using Firebase's new 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: //allinonescript.com/a/34105063

upvote
  flag
That's not quite right - the new query API allows you to order by any arbitrary child attribute, but they cannot currently be chained / combined. – Rob DiMarco
upvote
  flag
I tried the jsbin and it seems to be accepting multiple properties and it does a 'AND' inclusive filter. – 47d_
upvote
  flag
Hey @RobDiMarco. I was indeed wondering whether it was intentional that I could chain them. Could it be that it works because the second query is done client-side (since there is no index on that field)? – Frank van Puffelen
2 upvote
  flag
When this is officially released next week, multiple orderBy() calls will throw an error, because the clients currently give unexpected results. It's possible that it coincidentally worked in your test, but is not built to work this generically (though we'd love to add it!). – Rob DiMarco
1 upvote
  flag
is this still the case as of now Sep 2015? Cannot find any update on any new beta API – JDG
upvote
  flag
Yup. No changes. – Frank van Puffelen
upvote
  flag
@FrankvanPuffelen Thanks for reply. I have just got to use Firebase so I am excited to see what is in the future roadmap of development. – JDG
11 upvote
  flag
Is this still relevant in 2016 with Firebase V3? Aren't there better ways to do this? – Pier
2 upvote
  flag
It's still equally relevant. – Frank van Puffelen
1 upvote
  flag
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/… – David East
upvote
  flag
Good point. Added – Frank van Puffelen
16 upvote
  flag
Why we can't use .equalTo('comedy') instead of .startAt('comedy').endAt('comedy')? – JCarlos
upvote
  flag
How I can use via restApi – Irfan
upvote
  flag
@JCarlos They're equivalent. – Ionoclast Brigham
upvote
  flag
Hi all, I wanted to reference this post as well to see if these answers apply: //allinonescript.com/questions/42016194/… – tccpg288
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) {

    }
});
3 upvote
  flag
There is no getLead() method on DataSnapshot object. – Parag Kadam
upvote
  flag
He converted it to Movie object and assuming there is a getLead() metho. – Kim G Pham

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));
upvote
  flag
Any TypeScript definitions available for your library? – Levi Fuller
upvote
  flag
It's written in TS! So just import :) – David East
upvote
  flag
I'm getting a Buffer is undefined error even thought the Buffer typings are resolving Buffer correctly. I did have to modify the getKey() reference from this.getKey = () => this.ref().getKey(); to this.getKey = () => this.ref().key(); assumingly because of a different version of firebase, but I don't think that would affect it. – Levi Fuller
upvote
  flag
Before I get too invested, does Querybase support something like: let ref = this.firebase.child('test/checkpoints'); // example Checkpoint = {name: "Building an app", tags: {{tier: "1", "name": "apps"}, {tier: "2", "name": "angular2"}}} let querybase = new Querybase(ref, ['tags']); let queryRef = querybase.where({tags: {tier: "1", name: "apps"}}); queryRef.on('value', snap => console.log(snap)); – Levi Fuller
upvote
  flag
This is only Firebase 3.0 compatible. I may need to include an ambient declaration for Buffer for browser users. – David East
upvote
  flag
Just realized my typings for Firebase were old. Need to find some newer ones. EDIT: I guess your library is also using the same Firebase typings 2.4.1 – Levi Fuller
upvote
  flag
@LeviFuller Yeah, because I only rely on the Database Reference which API hasn't changed too much. I modified the small changes locally. The official typings haven't been shipped yet. We do have unofficial typings in the AngulaFire2 library though. – David East
upvote
  flag
upvote
  flag
@David East - I'm using Polymer, what's the best way to handle AND queries (with or without your QueryBase) ? – Denis Truffaut
upvote
  flag
@DavidEast all these filter hacks works on Android firbase clients? filter the hotel search filter on location stars, rating etc ?? – LOG_TAG
upvote
  flag
@DavidEast I tried to import in typescript but it's not working. only .js files exist in node_modules. How to use it? – Nicholas
1 upvote
  flag
do you know why they made the database so restrictive ? – Ced
upvote
  flag
Any option to do an IOS Swift/Android version – mding5692
upvote
  flag
Hi David, can you please tell me how we can add push key to the object while using querybaseRef.push(payload) .. As we can do with normally ref.push().key in firebase db – Rizwan Jamal
upvote
  flag
Hi David, after npm install querybase --save when I import it like import * as querybase from 'querybase'; got typeErrors 'cannot found module' and I found that under node_modules all files having extension .js why the module not contains .ts files ? – Rizwan Jamal

Not the answer you're looking for? Browse other questions tagged or ask your own question.