What's the reason that firebase can not do multiple WHERE condition?

[Tim Lai]

I have ask this question in stack overflow, but it's not a good place to ask this question, so please allow me to ask the same question here.

It's quite interesting that when people ask this kind of question on internet the answers are like:

1. It's NoSQL, not relational database.
   
2. Please structure your database
   
3. Add some combination key to your data.
   

Thinking how to structure database is really interesting for me, however I am curious about is it really difficult to implement multiple query on firebase? is there a technical issue, performance issue, or any policy considerations ?

For example, there are three approaches to solve this question:

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.
   

And in this question, proper data structure will allow you to query by multiple fields.

I have no problem with these solutions above if my app is not published yet. What if my app already online for 10 months? Should I redesign my database structure? Setup a backend sever or use cloud functions to migrate all the data or insert some combination keys in order to fit new query constraint?

If I setup a database structure below in parse server, I can do multiple query and sort without redesign my database or insert new combination keys. Parse and Firebase are all nosql database, but Parse can give multiple constraints where Firebase can not.

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

English isn’t my first language, so please excuse any mistakes. 

What I want to ask is not about how to structure database to fit query constraints, I am interested in why firebase can't do that? because it's a real-time db? multiple query constraints cost a lot? or there are some limitations from the architecture of Firebase?

[Damien Lebrun]

There's reason. They just didn't implement it. You need to build your "indexes" yourself.

Note that it's not a matter of restructuring your data; you simply need to build a new "index" when you want to support a new query. The canonical location for a movie can be "/movies/details/$movieID" and you duplicate the data to "/movies/byGenre/$genreID/$movieID" and "/movies/byLead/$leadID/$movieID" if you need to query by genre+lead and by lead+genre. When you need a new complex query, you just add a new collection, e.g. "/moviesByDirector/$directorID/$movieID".

Other NOSQL DBs like Google Datastore can manage indexes for you.