Query based on multiple where clauses in Firebase

Question

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

score 289 · Accepted Answer · edited Feb 16 '21 at 02:11

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.

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, Nov 02 '14 at 18:59
I tried the jsbin and it seems to be accepting multiple properties and it does a 'AND' inclusive filter. — 47d_, Nov 02 '14 at 21:43
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, Nov 02 '14 at 21:54
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, Nov 02 '14 at 23:37
is this still the case as of now Sep 2015? Cannot find any update on any new beta API — JDG, Sep 17 '15 at 14:42
@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, Sep 18 '15 at 15:32
Is this still relevant in 2016 with Firebase V3? Aren't there better ways to do this? — Pier, Jun 03 '16 at 18:21
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: https://github.com/davideast/Querybasehttps://github.com/davideast/Querybase — David East, Jun 06 '16 at 13:33
Why we can't use ```.equalTo('comedy')``` instead of ```.startAt('comedy').endAt('comedy')```? — JCarlosR, Nov 01 '16 at 14:33
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 '18 at 21:47
@Snake no you didn't. I don't know why but Google is pretty slow in making Firebase database better. I know Firestore but it just isn't that good. Lacks good querying. — Srujan Barai, Apr 17 '18 at 23:12
@Snake Feb 2019 and the problem still isn't fixed.... Google are too slow. — Supertecnoboff, Feb 14 '19 at 10:17
I know the original question was about the real-time database, but is this also a limitation of Google Cloud Firestore? — Simon East, Jul 10 '19 at 04:04
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 https://firebase.google.com/docs/firestore/query-data/queries#compound_queries — Frank van Puffelen, Jul 10 '19 at 04:09

score 63 · Answer 2 · edited Oct 29 '21 at 15:10

63

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

edited Oct 29 '21 at 15:10

Ericgit

6,089
2
42
53

answered Jun 06 '16 at 13:38

David East

31,526
6
67
82

1

Any TypeScript definitions available for your library? – Levi Fuller Jun 19 '16 at 20:29
2

It's written in TS! So just import :) – David East Jun 19 '16 at 20:30
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 Jun 19 '16 at 21:11
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 Jun 19 '16 at 21:17
This is only Firebase 3.0 compatible. I may need to include an ambient declaration for Buffer for browser users. – David East Jun 19 '16 at 21:17
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 Jun 19 '16 at 21:20
@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 Jun 19 '16 at 21:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115056/discussion-between-levi-fuller-and-david-east). – Levi Fuller Jun 19 '16 at 21:35
@David East - I'm using Polymer, what's the best way to handle AND queries (with or without your QueryBase) ? – Denis Truffaut Aug 28 '16 at 14:19
@DavidEast all these filter hacks works on Android firbase clients? filter the hotel search filter on location stars, rating etc ?? – LOG_TAG Nov 01 '16 at 11:20
@DavidEast I tried to import in typescript but it's not working. only .js files exist in node_modules. How to use it? – Nicholas Apr 28 '17 at 18:55
11

do you know why they made the database so restrictive ? – Ced Aug 21 '17 at 21:38
2

Any option to do an IOS Swift/Android version – mding5692 Sep 12 '17 at 19:46
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 Nov 29 '17 at 12:44
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 Nov 29 '17 at 13:11
Hi David can we filter on nested fields ? – Fadi Jan 28 '18 at 10:30
2

David, any android/Java equivalence to it? – Snake Feb 24 '18 at 21:48
Can we use QueryBase on Android projects? – Filippos Mar 08 '18 at 15:42
The problem with this is that it requires cloud code..... which can sometimes be slow on Firebase. – Supertecnoboff Feb 14 '19 at 10:20
Is there any similar library to Dart? – exequielc Nov 17 '19 at 11:40
I just installed via npm install querybase --save but am confused for the next steps how to make querybase available? The documentation don't provide clear guidelines – kushal Baldev Jul 27 '20 at 11:51

score 9 · Answer 3 · edited Aug 04 '17 at 08:19

9

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

    }
});

edited Aug 04 '17 at 08:19

Mehmed

2,880
4
41
62

answered Jan 20 '16 at 14:22

Andrew Lam

3,675
4
23
34

4

There is no getLead() method on DataSnapshot object. – Parag Kadam Jul 31 '16 at 13:59
5

He converted it to Movie object and assuming there is a getLead() metho. – Kim G Pham Dec 14 '17 at 22:20
2

Thankz a lot. It's working such as more where clauses – Nuwan Withanage Jul 18 '18 at 06: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 '21 at 20:01

score 4 · Answer 4 · edited Feb 16 '21 at 02:17

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"
      ]
    }
  }
}

score 4 · Answer 5 · edited Jul 24 '22 at 14:05

For Cloud Firestore

https://firebase.google.com/docs/firestore/query-data/queries#compound_queries

Compound queries You can chain multiple equality operators (== or array-contains) methods to create more specific queries (logical AND). However, you must create a composite index to combine equality operators with the inequality operators, <, <=, >, and !=.

citiesRef.where('state', '==', 'CO').where('name', '==', 'Denver');
citiesRef.where('state', '==', 'CA').where('population', '<', 1000000);

You can perform range (<, <=, >, >=) or not equals (!=) comparisons only on a single field, and you can include at most one array-contains or array-contains-any clause in a compound query:

This is correct, although it would be better to put code in codeblocks. — John Miller, Mar 24 '22 at 01:22

score 2 · Answer 6 · answered Apr 14 '20 at 16:28

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

score 1 · Answer 7 · answered Apr 14 '21 at 12:11

The data from firebase realtime database is as _InternalLinkedHashMap<dynamic, dynamic>. You can also just convert this it to your map and query very easily.

For example, I have a chat app and I use realtime database to store the uid of the user and the bool value whether the user is online or not. As the picture below.

Now, I have a class RealtimeDatabase and a static method getAllUsersOnineStatus().

static getOnilineUsersUID() {
var dbRef = FirebaseDatabase.instance;
DatabaseReference reference = dbRef.reference().child("Online");
reference.once().then((value) {
  Map<String, bool> map = Map<String, bool>.from(value.value);
  List users = [];
  map.forEach((key, value) {
    if (value) {
      users.add(key);
    }
  });
  print(users);
});

}

It will print [NOraDTGaQSZbIEszidCujw1AEym2]

I am new to flutter If you know more please update the answer.

score -2 · Answer 8 · answered Oct 23 '18 at 09:33

-2

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

This will work.

answered Oct 23 '18 at 09:33

jaleel

1,169
8
22
46

Query based on multiple where clauses in Firebase

8 Answers8

1. filter most on the server, do the rest on the client

2. add a property that combines the values that you want to filter on

3. create a custom index programmatically

Meet Querybase!

Linked

Related