How to perform compound queries with logical OR in

Question

From the docs:

You can also chain multiple where() methods to create more specific queries (logical AND).

How can I perform an OR query? Example:

1. Give me all documents where the field status is open OR upcoming
2. Give me all documents where the field status == open OR createdAt <= <somedatetime>

Answer 1

With the recent addition of IN queries, Firestore supports "up to 10 equality clauses on the same field with a logical OR"

A possible solution to (1) would be:

documents.where('status', 'in', ['open', 'upcoming']);

See Firebase Guides: Query Operators | in and array-contains-any

Answer 2

I would have no "status" field, but status related fields, updating them to true or false based on request, like

{ name: "a", status_open: true, status_upcoming: false, status_closed: false}

However, check Firebase Cloud Functions. You could have a function listening status changes, updating status related properties like

{ name: "a", status: "open", status_open: true, status_upcoming: false, status_closed: false}

one or the other, your query could be just

...where('status_open','==',true)...

Hope it helps.

Answer 3

We have the same problem just now, luckily the only possible values for ours are A,B,C,D (4) so we have to query for things like A||B, A||C, A||B||C, D, etc

---

As of like a few months ago firebase supports a new query array-contains so what we do is make an array and we pre-process the OR values to the array

if (a) {
array addObject:@"a"
}
if (b) {
array addObject:@"b"
}
if (a||b) {
array addObject:@"a||b"
}
etc

And we do this for all 4! values or however many combos there are.

THEN we can simply check the query [document arrayContains:@"a||c"] or whatever type of condition we need.

So if something only qualified for conditional A of our 4 conditionals (A,B,C,D) then its array would contain the following literal strings: @["A", "A||B", "A||C", "A||D", "A||B||C", "A||B||D", "A||C||D", "A||B||C||D"]

Then for any of those OR combinations we can just search array-contains on whatever we may want (e.g. "A||C")

---

Note: This is only a reasonable approach if you have a few number of possible values to compare OR with.

More info on Array-contains here, since it's newish to firebase docs

Answer 4

suggest to give value for status as well.
ex.

{ name: "a", statusValue = 10, status = 'open' }

{ name: "b", statusValue = 20, status = 'upcoming'}

{ name: "c", statusValue = 30, status = 'close'}

you can query by ref.where('statusValue', '<=', 20) then both 'a' and 'b' will found.

this can save your query cost and performance.

btw, it is not fix all case.

Answer 5

OR isn't supported as it's hard for the server to scale it (requires keeping state to dedup). The work around is to issue 2 queries, one for each condition, and dedup on the client.

---

Edit (Nov 2019):

Cloud Firestore now supports IN queries which are a limited type of OR query.

For the example above you could do:

// Get all documents in 'foo' where status is open or upcmoming
db.collection('foo').where('status','in',['open','upcoming']).get()

However it's still not possible to do a general OR condition involving multiple fields.

Answer 6

you can bind two Observables using the rxjs merge operator. Here you have an example.

import { Observable } from 'rxjs/Observable';
import 'rxjs/add/observable/merge';

...

getCombinatedStatus(): Observable<any> {
   return Observable.merge(this.db.collection('foo', ref => ref.where('status','==','open')).valueChanges(),
                           this.db.collection('foo', ref => ref.where('status','==','upcoming')).valueChanges());
}

Then you can subscribe to the new Observable updates using the above method:

getCombinatedStatus.subscribe(results => console.log(results);

I hope this can help you, greetings from Chile!!