Firebase multiple WHERE clause in query

Question

I'm trying to retreive data where flight data is returned only when the arrival date and airport match. I can't seem to figure out the best solution for this. I can only pull data where either the airport or arrival date is the same, not both (can only use equalTo() once). Here is what my current java code looks like:

Firebase ref = mFirebaseRef.child(FirebaseReference.CHILD_FLIGHTS);

Query queryRef = ref.orderByChild("airport").equalTo(getAirport());

queryRef.addListenerForSingleValueEvent(new ValueEventListener() {
    @Override
    public void onDataChange(DataSnapshot dataSnapshot) {
        System.out.println(TAG + " datasnapshot is equal to " + dataSnapshot);
    }
}

Here's the data itself:

{
   "flight":{
      "1ddf3c02-1f2e-4eb7-93d8-3d8d4f9e3da2":{
         "airport":"Gothenburg, Sweden - Landvetter (GOT)",
         "arrivalDate":"2016-06-21",
         "arrivalTime":"20:58",
         "code":"GOT",
         "departureDate":"2016-06-23",
         "departureTime":"20:58"
      },
      "c2c86e54-b4d0-4d83-934b-775a86f0a16c":{
         "airport":"Gothenburg, Sweden - Landvetter (GOT)",
         "arrivalDate":"2016-06-21",
         "arrivalTime":"20:50",
         "code":"GOT",
         "departureDate":"2016-06-23",
         "departureTime":"20:50"
      }
   },
   "users":{
      "1ddf3c02-1f2e-4eb7-93d8-3d8d4f9e3da2":{
         "age":"25",
         "createdTime":"1466533088358",
         "email":"test2@test.com",
         "provider":"password",
         "sex":"M",
         "username":"user2"
      },
      "c2c86e54-b4d0-4d83-934b-775a86f0a16c":{
         "age":"25",
         "createdTime":"1466374588255",
         "email":"test1@test.com",
         "provider":"password",
         "sex":"M",
         "username":"user1"
      }
   }
}

The current java code will return all children which only have the same airport. As you could guess, this isn't feasible when the amount of data which will need to be sorted client side is much larger than the above test data. How can I better filter the data on firebase's end?

score 11 · Accepted Answer · answered Jun 21 '16 at 19:19

The Realtime Database does not support multiple where clauses but you can create an extra key to make it possible.

A "flight" in your "flight" list can have a combined key for "arrivalDate" and "code".

"1ddf3c02-1f2e-4eb7-93d8-3d8d4f9e3da2" : {
  "airport" : "Gothenburg, Sweden - Landvetter (GOT)",
  "arrivalDate" : "2016-06-21",
  "arrivalTime" : "20:58",
  "arrivalDate_code": "2016-06-21_GOT", // combined key
  "code" : "GOT",
  "departureDate" : "2016-06-23",
  "departureTime" : "20:58"
}

Then you can query for that key.

Firebase ref = mFirebaseRef.child(FirebaseReference.CHILD_FLIGHTS);
Query queryRef = ref.orderByChild("arrivalDate_code").equalTo("2016-06-21_GOT");
queryRef.addListenerForSingleValueEvent(new ValueEventListener() {
   @Override
   public void onDataChange(DataSnapshot dataSnapshot) {

   }
});

Another option is to key off of a field in the data structure:

/ flights
  / $code
    / $flight_id

This means your data would look like this:

"flight" : {
  "GOT": {
    "1ddf3c02-1f2e-4eb7-93d8-3d8d4f9e3da2" : {
      "airport" : "Gothenburg, Sweden - Landvetter (GOT)",
      "arrivalDate" : "2016-06-21",
      "arrivalTime" : "20:58",
      "code" : "GOT",
      "departureDate" : "2016-06-23",
      "departureTime" : "20:58"
    }
  }
}

And then you could formulate a query like this:

Firebase ref = mFirebaseRef.child(FirebaseReference.CHILD_FLIGHTS);
Query queryRef = ref.child("GOT").orderByChild("arrivalTime").equalTo("2016-06-21_GOT");
queryRef.addListenerForSingleValueEvent(new ValueEventListener() {
   @Override
   public void onDataChange(DataSnapshot dataSnapshot) {

   }
});

you mean this is https://github.com/davideast/Querybase not possible in android? Just want to confirm ! — LOG_TAG, Nov 01 '16 at 11:43
I hope firebase soon incorporates a built in way to write multiple where clause instead of a work around like these. — Parag Kadam, Nov 29 '16 at 07:26

score 0 · Answer 2 · answered Jun 21 '16 at 19:19

0

Consider to use nested listeners: First you can equalTo to one of them, keep results in a variable, and in the nested listener call equalTo the second and check against your results.

This way you can do And query.

answered Jun 21 '16 at 19:19

Ami Hollander

2,435
3
29
47

3

It's an anti-pattern to nest data or to create nested listeners with the realtime database, I recommend re-thinking the data structure to keep a flat structure with a single read. – David East Jun 21 '16 at 19:20
3

Yes you are right, thank you. – Ami Hollander Jun 22 '16 at 16:03

Firebase multiple WHERE clause in query

2 Answers2

Linked

Related