Query Firebase Database with two between conditions

Question

I'm wondering if it's possible to return a list of values from my Firebase database where each element satisfies two conditions.

For example, if my database looked like this:

MYAPP
|_______________items
|                   |_____OshwYF72Jhd9bUw56W7d
|                   |                   |
|                   |                   |__item_name:"plank_5"
|                   |                   |__length:"120"            
|                   |                   |__width:"50"             
|                   |                            
|                   |_____KbHy4293dYgVtT9pdoW
|                   |_____PS8tgw53SnO892Jhweh
|                   |_____Gicuwy8r23ndoijdakr
|
|___customers

And I want to query the database for the item_name of every item that has a length of between 100-150 and a width of between 30-70, is there a way for me to do this with a Firebase query?

I've read the answers here: Query based on multiple where clauses in firebase but that doesn't deal with multiple between/range clauses, which is what I would need in the above scenario.

I saw this plugin in another answer: https://github.com/davideast/Querybase but the where clause doesn't seem to take a range of values. For example:

const queriedDbRef = querybaseRef
  .where({
    length: (between 100-150),
    width: (between 30-70)
  });

Is such a query even possible? Or will I have to get all items matching one condition, and then apply the second condition client-side, using Javascript?

Answer 1

A Firebase Database query can only order/filter on one property. That's a hard limit that is not likely to change in the near future.

In some cases it is possible to combine multiple values into a single property, such my answer you linked or in QueryBase. But in those cases as you found, you can only perform a relative/range query on a single of those values: i.e. the last one.

The only case where someone (to my knowing) has ever implemented multi-value range querying is in GeoFire, where we combine the longitude and latitude of a location into a so-called GeoHash. I highly recommend reading more about geohashes, because they do something quite unique: combine two numerical values, into a single string value that allows ordering and filtering on its numeric components.

From looking at the two values you want to combine (length and width), it might be possible to create a similar composite string from them. But requires that you find a way to combine the two values for your use-case. The simplest possible way I can think if would be to store the area, so length * width:

{
  items: {
    "OshwYF72Jhd9bUw56W7d": {
      item_name:"plank_5",
      length: 120, // you store length and width as strings, please fix that
      width: 50,
      area: 6000
    }
  }
}

That way you can filter for items with an area between 3000 (100x30) and 10500 (70x150):

var query = ref.orderBy("area").startAt(3000).endAt(10500);

This query will match too many items, so you will have to perform some extra filtering client-side to reject the mismatches (Geofire does the same btw):

query.on("child_added", function(snapshot) {
  var item = snapshot.val();
  if (item.length >= 100 && item.length <= 150
      && item.width >= 30 && item.width <= 70) {
    // TODO: do something with the item
  }
});