Question on how `Filter.or` works with inequality filter on multiple fields #2071

thisisgit · 2024-06-20T06:19:29Z

Hi all, I need your help on understanding how Filter.or works with inequality filter on multiple fields.

I have 3 matching documents for this query (Note that foo.date is firestore timestamp):

query.where('userId', '==', userId).where(
	Filter.and(
	  Filter.where('foo.date', '>=', from),
	  Filter.where('foo.date', '<=', to)
	)
)
// Returns 3 documents

And I have 0 document for this query:

query.where('userId', '==', userId).where(
	Filter.and(
	  Filter.where('bar.date', '>=', from),
	  Filter.where('bar.date', '<=', to)
	)
)
// Returns 0 document

And I’m expecting following query to return 3 documents but it returns 0:

query.where('userId', '==', userId).where(
	Filter.or(
		Filter.and(
		  Filter.where('foo.date', '>=', from),
		  Filter.where('foo.date', '<=', to)
		),
		Filter.and(
		  Filter.where('bar.date', '>=', from),
		  Filter.where('bar.date', '<=', to)
		),
  )
);
// Expecting 3 documents but returns 0

Querying for this requires me to create following composite indexes:

userId (Asc)
foo.date (Asc)
bar.date (Asc)
__name__ (Asc)

And the query explain shows as following:

PlanSummary {
  indexesUsed: [
    {
      query_scope: 'Collection',
      properties: '(userId ASC, foo.date ASC, bar.date ASC, __name__ ASC)'
    },
    {
      properties: '(userId ASC, foo.date ASC, bar.date ASC, __name__ ASC)',
      query_scope: 'Collection'
    }
  ]
}

ExecutionStats {
  resultsReturned: 0,
  executionDuration: { seconds: 0, nanoseconds: 31264000 },
  readOperations: 1,
  debugStats: {
    index_entries_scanned: '0',
    billing_details: {
      index_entries_billable: '0',
      min_query_cost: '1',
      documents_billable: '0',
      small_ops: '0'
    },
    documents_scanned: '0'
  }
}

Isn’t Filter.or suppose to return document if either 1st or 2nd case matches?

I tested this on firebase-admin at v12.1.1 and react-native-firebase at v20.0.0 but they all result the same.

The text was updated successfully, but these errors were encountered:

ehsannas · 2024-06-21T17:28:17Z

hey @thisisgit , the likely explanation is the following:

if you have

query1 = col where a>0
query2 = col where b>0.

and documents

doc1={a:1}
doc2={b:1}

Then query1 returns 1 document, query2 returns 1 document, but query3 = col where a>0 || b>0 will return 0 documents because as you mentioned, query3 uses a composite index a asc, b asc, __name__ asc -- which means this index will only contain documents that contain both field a and field b. When a field is missing in a document, that document cannot be indexed on that field. In this example, the composite index will not contain doc1 nor doc2.

if the documents are:
doc1={a:1, b:null}
doc2={a:null, b:1}

then query3 should return 2 documents.

I hope that answers your question. I'll close this issue, but feel free to let me know if this doesn't address your issue.

thisisgit · 2024-06-21T23:52:52Z

Thanks @ehsannas , that explains why it wasn't working as I expected.

What I'm trying to do here is to have v2 of a field without data migration.
So bar in my example is basically fooV2.
That means the documents after the release can have foo field as null, but documents created before the release won't have bar field at all.

Given the fact that the query won't return documents if any of the field is missing(undefined), the only solution I can think of right now is to have 2 separate queries of foo and bar. Which means it won't use inequality filter on multiple fields.
If I were to use query.onSnapshot to listen for real time changes, then I would have to merge documents returned from 2 different streams into 1.
And since I plan to migrate foo to bar on demand, as in it will only trigger if user modifies foo of a document, it will trigger both query.onSnapshots since they're both affected. This may cause multiple re-renders on single event and possibly missing data for a short time in between the merge triggered from foo<>bar. I would have to test them to see the real impact but for now, these are some of the possible issues from having 2 queries.

Is there any solution to address above issue with a single stream of onSnapshot? The only solution I can think of right now is to have 2 queries or to consider data migration but I want to keep them as my last option.

gregfenton · 2024-06-23T01:15:14Z

If this was me, part of "the new release" would be to run a script over existing docs adding the missing bar field (or fooV2 or whatever) giving them the "default value" or null.

Older apps won't care about that field and should just ignore it. It is good practice to have any update operation you do in a NoSQL database to update just the fields you know about and to leave anything else as is...don't "rewrite" the entire document for fear that you clobber data that later versions of your app will put there.

With this approach, your v1 app should be able to keep running even after your v2 app has been released. Both should work with the database just fine.

If your data model gets really complicated and there are a lot of inter-field relationships you need maintained in business logic in your app, then you'd need to come up with a way to force v1 users to upgrade (or whatever). Alternatively, you could be using server-side logic (e.g. a Cloud Function) to maintain those complex relationships and your v1 app would keep running along just fine.

thisisgit added priority: p3 Desirable enhancement or fix. May not be included in next release. type: question Request for information or clarification. Not an issue. labels Jun 20, 2024

product-auto-label bot added the api: firestore Issues related to the googleapis/nodejs-firestore API. label Jun 20, 2024

ehsannas self-assigned this Jun 21, 2024

ehsannas closed this as completed Jun 21, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Question on how `Filter.or` works with inequality filter on multiple fields #2071

Question on how `Filter.or` works with inequality filter on multiple fields #2071

Question on how Filter.or works with inequality filter on multiple fields #2071

Question on how Filter.or works with inequality filter on multiple fields #2071

Comments

Question on how `Filter.or` works with inequality filter on multiple fields #2071

Question on how `Filter.or` works with inequality filter on multiple fields #2071