I know that there's no real logical "OR" in Firestore queries. That's why I usually do two separate queries and merge the results on the client side.

However I need to add pagination now. For pagination, merging the results from Firestore on the client side will not work obviously.

EDIT: Some more information on my database scheme:

// Collection: 'items'
doc-id-1
   provider_id: 'ABCDE'
   company_id: 'FGHIJ'

doc-id-2
   provider_id: 'KLMNO'
   company_id: 'ABCDE'

doc-id-3
   provider_id: 'ABCDE'
   company_id: 'ABCDE'

doc-id-4
   provider_id: 'KLMNO'
   company_id: 'KLMNO'

Here's my example:

// Get items by provider
db_query = this.db.collection('items').ref
    .where('provider_id', '==', 'ABCDE');

// OR

// Get items by company
db_query = this.db.collection('items').ref
    .where('company_id', '==', 'ABCDE');

/* 
   Combining the queries above with a logical OR should return
   the following documents (from my database scheme further up):
     - doc-id-1
     - doc-id-2
     - doc-id-3
*/

I need to combine these queries (get items, where provider_id OR company_id has a given value).

My pagination works as the following (default approach in Firestore):

// Query type (init, next page, prev page, fallback)
if (query_type == 'init') {
  db_query = db_query.limit(limit);

} else if (query_type == 'next') {
  db_query = db_query.startAfter(last_in_response).limit(limit);

} else if (query_type == 'prev') {
  db_query = db_query.endBefore(first_in_response).limitToLast(limit);

} else {
  db_query = db_query.limit(limit);
}

Is there any way to solve this? If I still combine the results on the client side, my pagination cursors won't be correct. Therefore I need to somehow cascade the query but I can't find the right approach for that.

Thank you in advance for any hints!

CodePudding user response：

I need to combine these queries (get items, where provider_id OR company_id has a given value).

In your specific case (i.e. searching for all docs in the same items collection where provider_id or company_id equals the same value) you could denormalize your data and, for example, have an extra field provider_company_ids of type array, with two elements. The first element will hold the value of provider_id and the second element will hold the value of company_id.

Then you can use the array-contains operator as follows:

db_query = this.db.collection('items').ref
.where("provider_company_ids", "array-contains", "ABCDE");

and you'll be able to correctly paginate since it's now a single query.

Update following your comment added under your question:

After implementing the above solution, your docs will look as follows:

// Collection: 'items'
doc-id-1
   provider_id: 'ABCDE'
   company_id: 'FGHIJ'
   provider_company_ids: ['ABCDE', 'FGHIJ']  // New field of type array

doc-id-2
   provider_id: 'KLMNO'
   company_id: 'ABCDE'
   provider_company_ids: ['KLMNO', 'ABCDE']

doc-id-3
   provider_id: 'ABCDE'
   company_id: 'ABCDE'
   provider_company_ids: ['ABCDE', 'ABCDE']

doc-id-4
   provider_id: 'KLMNO'
   company_id: 'KLMNO'
   provider_company_ids: ['KLMNO', 'KLMNO']

PS: I'm not sure what is the ref property in your code (db_query = this.db.collection('items').ref.where('provider_id', '==', 'ABCDE');). I re-used it as such in my anwser, making the assumption that this.db.collection('items').ref returns a Query.