How to perform compound queries with logical OR in Cloud Firestore?

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>

Solution 1

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.

Solution 2

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

Solution 3

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.

Solution 4

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.

Solution 5

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

View more solutions

Comments

[jwehrle]

I had a similar question and now I'm editing my data schema so that the values are numbers with an intentional range (example: no access: 0, read access: 1, edit access: 2, owner access 3. Then I could query for an access field isGreaterThan, say, 1). Essentially, I'm thinking of leveraging the implicit OR in number ranges.

[charnould]

I understand the answer, but If I want to get all documents where the field status is a OR b OR c OR ... (let's say 50 OR). Do I need to perform 50 queries and join them? It doesn't appear super-optimal? Am I wrong? Is there a better way to approach this? Thx.

[abh]

@nerotulip any success on this ?

[bvaughn]

I'm also wondering if there's a better solution than running N queries and joining on the client. Seems very inefficient.

[Geek Guy]

This query should be availed to minimize firestore costs for developers

[rendom]

@GeekGuy Do you think Google wants less payments from their customers?

[Patricio Vargas]

any news on this?

[linus_hologram]

Would that still work with 5000 queries that have to be joined together afterwards?

[Doug Stevenson]

Dan, would you like to update this with the latest? :-)

[hkrlys]

@DougStevenson what is the latest ? is there a way to do so today ?

[Ankit]

@hkrlys Read kariem 's Answer below.

[ruslanys]

I may be biased, but I have a problem with the last edit: This answer was great for the time it was posted. However, there had been an addition to Firestore which I addressed in my answer on Dec 6 2019. I do not think this answer should have been edited. In addition, the edit happened in January, not in November as indicated in the header. Please see the discussion here: meta.stackexchange.com/questions/11474/…

[Dan McGrath]

@Kariem, Sam is a colleague of mine working directly on Firestore. I've been a tad busy and have had less time to update information around the web than I use to. Think of this as an extension of a self edit, which is fine to update to amend new and changing information. I've also upvoted your answer.

[PJP]

It helps more if you supply an explanation why this is the preferred solution and explain how it works. We want to educate, not just provide code.

[Johann]

The statement "it's hard for the server to scale" is nothing but a lame excuse for a lack of skills on Google's part. Just because your name is "Google" doesn't grant you automatic expertise in this field. Other indexing servers DO support the OR operator.