Search code examples
google-cloud-firestore

Firestore complex query with 8 fields. Is it doable and how?

Straigth away I aks that if this is impossible please just tell me. I understand that this might need maybe like 5 queries to be done and also filtering the data then on server yet (as they case will be with fields needing a full text search)

I'm trying to do a complex query with possibly +8 fiels. It is used in reporting view where there are many fields for user to choose how he wants the data to be filtered. It is used for 'Orders' collection that is the main collection of the whole db and will contain thoudands or tens of thousands of documents with 33 fields.

I would need to query the collection with the following fields

  1. customerName (full text search)
  2. deviceID (queried with array of devices so need to use "in" operator)
  3. minDate (greated than)
  4. maxDate (smaller than)
  5. ordererName (full text search)
  6. postalDistrict (full text search)
  7. product (equality)
  8. status (equality)

Then the query has conditional part of which 1 should match. As Firestore cannot handle or operation this (each) will be run in a separate query and such will be extended to the original one. So this means there will be 1 more field added to the query.

  1. customerID (equlity. If the user is customer's admin user then this will be added. Othetwise (at least) 1 of the rest 3 should match)
  2. deviceID (user can see only certain devices so this is another device array)
  3. locationID (user can see only certain locations so this is an array)
  4. ordererID (equality)

And the result needs to be ordered by createdAt value (timestamp) or if it would work better id of the document (used as name) which is a number of value 10 000 and up.

So possibly 9 fields to query with (at the moment, might be increased). Is Firestore is a big product I think it should have thought a pattern to handle such cases. As far as I understand now I need to create index for each unique query. With a query of 12+ fields there will be hundreds or thoudands of indexes needed (not completely sure how many).

Thank you for help!

EDIT For full text search I have been making an array of possible combinations: text => ["t", "te", "tex", "text"]

I had previously a query with fewer query fields and it worked. After adding more I expected it to work still.

Solution

  • I came to the conclusion that Firestore is not able to handle such complicated queries. It is an easy-to-setup database that scales well but is not able to do some parts of a normal SQL database. In the end, I ended up using the basic and old database model, MySQL, which could tackle this query issue easily. 

    Note: I would hope the users, and specifically the people working at Firebase, would mention this and other limitations when asked about them. It is not a shame to tell customers that our product is not meant for this purpose. It will build a much better image and trust than to just remain silent about the "hard question." Thank you.