Implementing OR in firestore query - Firebase firestore

Question

I am trying implement logical OR operator in firestore query.

db.collection('users').where('company_id', '==', companyId)
                          .where('role', '==', 'Maker')
                          .where('role', '==', 'Checker')
                          .where('role', '==', 'Approver')
                          .get().then(function(adminsSnapshot){


             //Some processing on dataSnapshot
})

But this is not the right way to implement OR.

I want all users with roles of 'Maker, Checker or Approver'.

How would i implement OR in firestore query ? There's nothing in Doc.

Two years later, this is now possible. See my updated answer. — Sam Stern, Dec 16 '19 at 18:11

Sam Stern · Accepted Answer · 2019-12-16T18:11:07.133

26

Edit (November 2019) Cloud Firestore now supports "IN" queries (announcement) which allows you to do a type of OR queries that look for documents with one of a few values on the same field.

For example for the query above:

db.collection('users')
  .where('company_id', '==', companyId)
  .where('role', 'in', ['Maker', 'Checker', 'Approver']);

Original answer

There is no "OR" query in Cloud Firestore. If you want to achieve this in a single query you will need a single field like maker_or_checker_or_approver: true.

Of course you can always do three queries and join them on the client.

edited Dec 16 '19 at 18:11

answered Oct 30 '17 at 16:07

Sam Stern

24,624
13
93
124

I am making 3 queries. That's what I am doing. Yes – Noman Ali Oct 31 '17 at 05:57
1

So, if I have let's say something like twitter, and i want to see only the tweets of people I follow, i can not say `tweets.where(user == john OR lisa OR julia)`? – Jürgen Brandstetter Nov 16 '17 at 11:28
Hi Guys. Has there been any updates to the node SDK yet? Multiple queries still seems an inefficient way to filter on multiple fields! – Darren Ackers Oct 31 '18 at 21:15
You can track progress on the OR feature here: https://issuetracker.google.com/issues/129070817 – Kevin Chen Jun 20 '19 at 17:42

score 1 · Answer 2 · answered Nov 08 '19 at 03:28

1

This is now being made possible in Firestore by newly-added support for both the in and array-contains-any operators, which allow querying for up to 10 values in a single query.

https://firebase.googleblog.com/2019/11/cloud-firestore-now-supports-in-queries.html

So, using your example, the query would look something like this.

db.collection('users')
    .where('company_id', '==', companyId)
    .where('role', 'in', ['Maker', 'Checker', 'Approver']);

In the above example substitute in for array-contains-any if your data is stored in an array.

answered Nov 08 '19 at 03:28

cokeman19

2,405
1
25
40

I am trying to use your query but get error "Invalid value "in" provided to function Query.where() for its second argument. Acceptable values: <, <=, ==, >=, >, array-contains". please help me how can i resolve this error – Yousuf Nov 26 '19 at 21:29
1

It may not be implemented in the client library you're using yet. Last I checked, it was only implemented in the latest versions of the NodeJS and Python SDKs. It'll likely roll out to more client libraries as time goes by. – cokeman19 Nov 26 '19 at 21:41

score 1 · Answer 3 · answered Mar 25 '23 at 02:22

1

FTI OR queries now available in Preview :)

Source: https://cloud.google.com/firestore/docs/release-notes#March_24_2023

answered Mar 25 '23 at 02:22

OS4LIFE

11
3

score 0 · Answer 4 · answered May 26 '18 at 14:25

0

You could combine the observables using rxjs, see angular(6) example below;

orQuery(){

    const $one = this.afs.collection("users", ref => ref.where("company_id","==","companyId")).valueChanges();
    const $two = this.afs.collection("users", ref => ref.where("role","==","Maker")).valueChanges();

    return combineLatest($one,$two).pipe(
        map(([one, two]) => [...one, ...two])
    )
}

getOr(){
    this.orQuery().subscribe(data => console.log(data))
}

answered May 26 '18 at 14:25

phicon

3,549
5
32
62

or... ```const combinedList = combineLatest(fooPosts, barPosts).pipe( map(arr => arr.reduce((acc, cur) => acc.concat(cur))))``` – Jonathan May 03 '20 at 21:11
1

How about paginate data using query cursors and implement OR on multiple fields? – A.W. Oct 18 '20 at 14:29

Implementing OR in firestore query - Firebase firestore

4 Answers4

Linked

Related