CODEWITHSUNDEEP
firebasegoogle-cloud-firestore
Teebs
Teebs

Reputation: 785

Firebase Firestore Query Date and Status not equals. Cannot have inequality filters on multiple properties

I want to query by date and status. I created an index for both the fields, but when querying it throws an error:

Cannot have inequality filters on multiple properties: [created_at, status]

const docQuery = db.collection('documents')
  .where('created_at', '<=', new Date()) // ex. created_at later than 2 weeks ago
  .where('status', '!=', 'processed') // works when == is used 
  .limit(10);

'status' is a string and can be multiple things.

I read about query limitations, https://firebase.google.com/docs/firestore/query-data/queries but that is such a simple thing for any database to do, what is a good solution for such a problem? Loading entire records is not an option.

Upvotes: 0

Views: 457

Answers (1)

Doug Stevenson
Doug Stevenson

Reputation: 317427

The issue isn't that it's a "simple" thing to do. The issue is that it's an unscalable thing to do. Firestore is fast and cheap because of the limitations it places on queries. Limiting queries to a single inequality/range filter allows it to scale massively without requiring arbitrarily large amounts of memory to perform lots of data transformations. It can simply stream results directly to the client without loading them all into enough memory to hold all of the results. While it's not necessary to understand how this works, it's necessary to accept the limitations if you want to use Firestore effectively.

For your particular case, you could make your data more scalable to query by changing to your "status" field from a single string field to a set of boolean fields. For each one of the possible status values, you could instead have a boolean field that indicates the current status. So, for the query you show here, if you had a field called "isProcessed" with a boolean value true/false, you could find the first 10 unprocessed documents created before the current date like this:

const docQuery = db.collection('documents')
  .where('created_at', '<=', new Date())
  .where('processed', '==', false)
  .limit(10);

Yes, this means you have to keep each field up to date when there is a change in status, but you now have a schema that's possible to query at massive scale.

Upvotes: 2

Related Questions