Defining a Firestore query by comparing two document's fields values

user_780099 0
Viewed 2711

I'm having my first steps with Cloud Firestore, and I am stuck on this one. I'm looking for a where query to return me values that are >= the limit.

  [{
    'limit': 100,
    'value': 200 
  }, 
  {
    'limit': 50,
    'value: 50,
  },
  {
    'limit': 95,
    'value': 90, 
  }]

I am using the Firebase Admin Python SDK in a Cloud Function.

Any hints?

google-cloud-functions google-cloud-firestore firebase python
2 Answers

As Alex has explained, there is no way to compare two document's fields values when executing a query with Firestore.

One possible approach, would be to save the result of the inequality when you save the document to the database. If, when you save a "check" document you know the two values (limit and value), you could add a third field named, for instance,valueAboveLimit with a value of True or False, that you calculate when you save the "check" document (i.e. from your front-end with the corresponding client SDK or from a server with the Firebase Admin Python SDK, depending on how you write the Firestore documents).

Then your query will be easy:

database = firestore.client()
col_checks = database.collection('checks')
query_checks = col_checks.where('valueAboveLimit', '==', True)
results = query_checks.get()

In case you don't have the values of limit and value at the time you save the Firestore document (for example the limit field is written later), you could use a Cloud Function that is triggered when the Firestore document is changed and, if the two fields are present, calculates and saves this valueAboveLimit field.

user_33718620

According to the earlier comments, you are looking for a way in which you can query the database so it can return only the documents where the value of limit property is >= than the value of value property. So according to you example, the expected result should be the first and the second document.

Unfortunately, there is currently no built-in query in Firestore that can help you achieve that. The simplest solution I can think of, would be to get all documents within your checks collection and filter the results client side. If you have in your collection a large number of documents, this might not be the best solution since it require to read all documents. This means that you be billed with one read operation for every document you read.

Another solution, might be to use a fiexed value for the limit property and query the database using a where() call:

col_checks.where('limit', '>=', your_limit)

And every time you need a new limit, you'll have to create a new query.

user_52468850
Related