FR: Firestore OR operator in WHERE query #321

EpicButterz · 2017-11-16T16:56:34Z

[REQUIRED] Describe your environment

Operating System version: N/A
Firebase SDK version: 4.6.2
Firebase Product: firestore

[REQUIRED] Describe the problem

Rather than having to make several separate queries I would like to see an OR operator for the WHERE query in Firestore. This is something that is currently available in MongoDB or with the $or operator here.

Relevant Code:

The query could look something like this:

citiesRef.where("state", "==", "CA", "||", "state", "==", "AZ")

or better yet

citiesRef.where("state == CA || state == AZ")

EpicButterz · 2017-11-16T17:18:52Z

For my specific case something more like an in operator would be more appropriate. I am looking to retrieve documents from a collection that correspond to a list of IDs. So something like:

citiesRef.in('id', ['P4ZoMnrNRQjnMGmuMmvc', 'VSw2KyP1YaBRnkheq5kB', 'i1658JL5a2Itv2l0BC8B'])

agersoncgps · 2017-11-24T18:58:36Z

OR and IN / CONTAINS / LIKE are pretty basic concepts in query languages. Confused why they are missing here.

mikelehen · 2017-11-28T00:25:25Z

Thanks for the feedback! These query improvements (OR, IN / CONTAINS, and LIKE queries) are all on our radar for future Cloud Firestore query improvements. I'm closing this issue as it's not specific to the JS SDK, but we know that these features would be very useful and there's a lot of demand for them. We intend to address them in the future as we continue to evolve Firestore's query functionality. Thanks!

dzoba · 2018-01-23T23:28:46Z

I would be very interested in these as well.

andreluisce · 2018-01-27T23:27:19Z

Any news about it?

taishikato · 2018-02-05T12:40:56Z

I am looking forward to this feature

atcults · 2018-02-13T03:28:54Z

Any news on ORing two filters ?

csoreff · 2018-02-14T08:30:55Z

I'd kill for a NOT IN [] >.<

katothompson · 2018-02-17T13:15:58Z

This would be a great feature to have!

schassande · 2018-02-19T12:36:39Z

I am looking forward to this feature too !

jkampitakis · 2018-02-24T17:46:47Z

Same here, I am looking forward to this feature.

cnolimit · 2018-03-09T22:35:52Z

can't wait to see the LIKE query addition

mdrideout · 2018-03-11T20:48:15Z

For anyone struggling for a solution, I mirrored the parts of firestore database relevant to search in elasticsearch. It's a search engine for no-sql database data, and can do WAY more than a "LIKE" or "WHERE" clause in a sql query. It's very customizable. Any additional data needed from the search results can then be pulled from firestore. In my app, elasticsearch handles any search queries, and data is pulled from firestore.

You can setup a stack on GCP relatively easily and for free, and now elasticsearch has their own stack deployment option (which can also use GCP, and provides more security options).
https://www.elastic.co/
https://bitnami.com/stack/elk

githubbla · 2018-04-02T10:00:38Z

I am looking forward to this feature too !

rtman · 2018-04-02T15:13:16Z

second all this.. OR is very necessary. Trying to concat lists after two WHERE queries is not great.

1001daysofcode · 2018-04-04T03:31:04Z

Can't wait for this feature!

m-a-zhou · 2018-04-08T16:45:58Z

This feature would be very helpful.

CyrusZei · 2018-04-11T23:46:35Z

any ETA on this ?

mikelehen · 2018-04-12T00:29:08Z

We can't provide ETAs on upcoming features, but as I said before, OR queries and IN queries are definitely on our radar and we appreciate all the interest.

For what it's worth, IN queries are likely more straightforward and so we may tackle them first, something along the lines of what @EpicButterz described in #321 (comment). I'd be curious to hear how many of you would find that useful or if you're looking for more general-purpose OR queries. And if you're looking for more general-purpose OR queries, some details on your use cases (e.g. examples of specific queries that you'd want to do in your app) could help us design the feature. Thanks!

csoreff · 2018-04-12T01:44:07Z

@mikelehen IN and NOT IN are more useful I think. OR would be nice to have but for now we can just combine the results of multiple queries for that.

mikelehen · 2018-04-12T01:51:03Z

Thanks. I'm curious if there's a strong desire for "NOT IN" (against a list of values) or if just "!=" (against a single value) would be sufficient for the use cases you have in mind.

csoreff · 2018-04-12T01:53:32Z

@mikelehen Let's say I have a list of keys for ignored items. I'd like to be able to easily grab all items where the key is NOT IN []. != is certainly desired as well, but they have different use cases. Right now I'm forced to grab all items and then manually filter out the ignored items on client side.

antonybudianto · 2018-04-12T02:01:08Z

i want a datastore like this:

members: [
  {
    id: 123
  },
  {
    id: 231
  }
],

teams: [
  {
    id: 1,
    members: [123, 231]
  },
  {
    id: 2,
    members: [123]
  }
]

but because there's no IN query, I've to duplicate the relationship:

members: [
  {
    id: 123,
    teams: [1, 2]
  },
  {
    id: 231,
    teams: [1]
  }
],

/* teams remain the same **/

my requirement is that:
a user must be able to know his/her teams
and a team must know its members.

mikelehen · 2018-04-12T02:13:48Z

@antonybudianto Thanks for the feedback. If I'm understanding correctly, I'd actually characterize this as a slightly different feature and the good news is that:

You can solve this today by modeling your teams as { id: 1, members: { "123": true, "231": true } } and then for user 123 to find all their teams you can do a query like teamsCollection.where("members.123", "==", true).
We're looking into adding a new feature that would let you query based on array values which would let you keep your desired schema (with arrays) and do something like teamsCollection.where("members", "array_contains", 123). I can't promise any timelines but this may launch before we get to OR / IN queries.

antonybudianto · 2018-04-12T02:42:30Z

Thanks for the solution. But one more, suppose I visit the team "A" page, and want to display its members there (I want to display their names/other fields, not ids), how do you query it (userCollections should contains no relationship object because teamcollections already have it)?

mikelehen · 2018-04-12T14:06:59Z

@antonybudianto Ahh, yeah. To get a member's name, etc. you'd need to look that up from the members collection presumably. Cloud Firestore doesn't support JOIN operations and that's unlikely to change in the foreseeable future. That said, if we added an IN query as described in #321 (comment) then you could look up all the members with a single additional query once you have their IDs.

Thanks for the feedback.

antonybudianto · 2018-04-12T14:13:48Z

@mikelehen yes, IN operation will be sufficient for my case, thanks for the consideration.

CyanDeveloper · 2019-02-22T11:11:11Z

+1

davidtlee · 2019-02-23T00:16:18Z

@mikelehen What I really need is the "IN" case. The "OR" case is manageable just by doing two queries and handling it on the frontend. But "IN" is really needed for eager loading. If I am querying a collection and for each of those collections, I need to do separate queries (e.g. to get authors for each post), that opens up tons of connections. Having an "IN" query would enable that to be done with just two queries.

timscullin · 2019-02-23T01:03:03Z

+1 so essential

long1eu · 2019-02-23T05:54:38Z

Using "add reaction" on the initial comment would increase priority while +1 comments are rather pointless and cause lots of people to get notified for no reason. To get notified yourself use the [Subscribe] button to the top right instead.

…

On Sat, 23 Feb 2019 at 03:03, timscullin ***@***.***> wrote: +1 so essential — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#321 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AI2nVkS057uP0Yo13I6tnGhVUoMYgyBjks5vQJNMgaJpZM4QgzlT> .

efraespada · 2019-02-24T04:18:06Z

+1 essential

permadiberlianto · 2019-03-06T08:26:39Z

+1, we need it

CyrusZei · 2019-03-07T09:03:30Z

could people please stop commenting on this and just add a Reaction instead?!?!

if you want this feature just add a 👍 on this comment and stop adding useless comments

jeremyfrancis · 2019-03-07T15:22:45Z

YES! Let's commit to terrorizing Firebase with our request for these features! Everyone like this post with a THUMBS UP and contact firebase directly!!!

sam26880 · 2019-03-07T16:32:41Z

+1

devpato · 2019-03-10T21:36:02Z

The fact that this issue has 135 comments plus all the reactions and Firestore team hasn't done anything about it's alarming.

Kinark · 2019-03-11T11:41:37Z

@devpato True. +1 anyway

thirajade · 2019-03-13T05:11:01Z

this feature is truely needed

awtmarriott · 2019-03-20T09:42:02Z

come on, just copy the MONGO "Find" syntax...

devpato · 2019-03-21T18:34:43Z

Decided to open the exact same issue since they closed this one and haven't seen any reply from firebase latetly #1624

rockwotj · 2019-03-21T22:01:09Z

Hey all,

Backend features should be filed at Cloud Firestore's public issue trackers.

I took the liberty of filing two features based on this GitHub issue:

It'd be great if you could hit the star button if you're interested in one or both features. Additionally if you have a specific usecase that you think would be useful for Google to keep in mind when building this feature please put it there. You can also CC yourself there to get updates from the team - as @mikelehen already has pointed out, this GitHub issue tracker is for the JS SDK, and this feature needs support in the backend. I'd encourage you to file other new feature requests that are backend specific in that issue tracker too!

davidtlee · 2019-03-21T22:15:22Z

Where is the Me too! button? I went to the link for the IN operator and don't see anything.

…

On Thu, Mar 21, 2019 at 3:01 PM Tyler Rockwood ***@***.***> wrote: Hey all, Backend features should be filed at Cloud Firestore's public issue trackers <https://cloud.google.com/support/docs/issue-trackers>. I took the liberty of filing two features based on this GitHub issue: - One for the IN operator within filters <https://issuetracker.google.com/issues/129056739> - One for the OR operator within filters <https://issuetracker.google.com/issues/129070817> It'd be great if you could hit the Me too! button if you're interested in one or both features. Additionally if you have a specific usecase that you think would be useful for Google to keep in mind when building this feature please put it there. You can also CC yourself there to get updates from the team - as @mikelehen <https://github.com/mikelehen> already has pointed out, this GitHub issue tracker is for the JS SDK, and this feature needs support in the backend. I'd encourage you to file other new feature requests that are *backend specific* in that issue tracker too! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#321 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AA0raSQm4AyngJRLZyKfhORxhyK9NP_Bks5vZAEtgaJpZM4QgzlT> .

-- David Lee Website: tech4good.soe.ucsc.edu Cell: (408) 857-1182

rockwotj · 2019-03-21T22:27:18Z

Sorry you actually have to hit the little star icon

mesqueeb · 2019-03-22T01:29:48Z

I humbly request all 81 participants of this thread (+158 persons who upvoted) to ⭐️ these issues, so that they can finally start looking into this!

jeremyfrancis · 2019-03-22T20:25:24Z

Hey all,

Backend features should be filed at Cloud Firestore's public issue trackers.

I took the liberty of filing two features based on this GitHub issue:

One for the IN operator within filters

One for the OR operator within filters

It'd be great if you could hit the star button if you're interested in one or both features. Additionally if you have a specific usecase that you think would be useful for Google to keep in mind when building this feature please put it there. You can also CC yourself there to get updates from the team - as @mikelehen already has pointed out, this GitHub issue tracker is for the JS SDK, and this feature needs support in the backend. I'd encourage you to file other new feature requests that are backend specific in that issue tracker too!

Just starred it. Everyone take a minute to do the same please!

darren-dev · 2019-03-29T09:19:53Z

Anyone reading this, please go to the following links and click on the STAR icon at the left of the title:
https://issuetracker.google.com/issues/129070817
https://issuetracker.google.com/issues/129056739

artolshansky · 2019-03-29T11:50:17Z

+1 (sub for news and in the issue tracker too)

jamesreilly5 · 2019-03-30T23:52:26Z

Thank you for raising the issue @darren-dev, starred and commented!

jamesreilly5 · 2019-04-04T10:42:27Z

Have raised another separate issue for NOT IN clause here - https://issuetracker.google.com/issues/129805446. Please star if this is blocking you too

darren-dev · 2019-04-05T07:07:10Z

@jamesreilly5 I may have a possible idea of a draft-fix, if you're open to entertaining the idea while your issue is there...

Basically, store the datePosted as a UNIX timestamp on each document, then on the user's end, each time they go to that page, update a field on the user such as dateSeen with the current UNIX timestamp.

Then you can easily do a where on the collection to only get the posts after a date (emulating only nre posts you haven't seen) such as

.collection('/posts').where('datePosted', '>=', user.dateSeen)

It's not perfect, but it's something :)

jamesreilly5 · 2019-04-13T03:15:37Z

@darren-dev yes ideally this would work but my number of posts per day is not that many so the newer posts would be quickly exhausted. My total number of posts if high (enough) though so if I can keep them random enough it'll work. I'll either take the hit of querying again when duplicates are fetched or think about how I can better distribute my randomness (currently a value between 1 and 1,000,000). I have some ideas in mind. Thanks for the suggestion

mpivchev · 2019-05-07T22:28:42Z

Firestore devs: "Let's pretend this request doesn't exist"

wilhuff · 2019-05-07T22:33:32Z

We're quite aware that this request exists, and are actively working on making this happen. Unfortunately, nearly all the work to support this on the backend where it's invisible.

We'll update here when we have something to share.

google-oss-bot added the feature-request label Nov 16, 2017

jshcrowthe added the api: firestore label Nov 16, 2017

mikelehen closed this as completed Nov 28, 2017

devpato mentioned this issue Mar 21, 2019

FR: Firestore OR operator in WHERE query #1624

Closed

wilhuff mentioned this issue Mar 22, 2019

FR: Query in operator firebase/firebase-ios-sdk#549

Closed

firebase locked and limited conversation to collaborators May 7, 2019

FR: Firestore OR operator in WHERE query #321

FR: Firestore OR operator in WHERE query #321

Comments

EpicButterz commented Nov 16, 2017 • edited by jshcrowthe Loading

[REQUIRED] Describe your environment

[REQUIRED] Describe the problem

Relevant Code:

EpicButterz commented Nov 16, 2017 • edited Loading

agersoncgps commented Nov 24, 2017

mikelehen commented Nov 28, 2017

dzoba commented Jan 23, 2018

andreluisce commented Jan 27, 2018

taishikato commented Feb 5, 2018

atcults commented Feb 13, 2018

csoreff commented Feb 14, 2018

katothompson commented Feb 17, 2018

schassande commented Feb 19, 2018

jkampitakis commented Feb 24, 2018

cnolimit commented Mar 9, 2018

mdrideout commented Mar 11, 2018 • edited Loading

githubbla commented Apr 2, 2018

rtman commented Apr 2, 2018

1001daysofcode commented Apr 4, 2018

m-a-zhou commented Apr 8, 2018

CyrusZei commented Apr 11, 2018

mikelehen commented Apr 12, 2018

csoreff commented Apr 12, 2018

mikelehen commented Apr 12, 2018

csoreff commented Apr 12, 2018 • edited Loading

antonybudianto commented Apr 12, 2018 • edited Loading

mikelehen commented Apr 12, 2018

antonybudianto commented Apr 12, 2018 • edited Loading

mikelehen commented Apr 12, 2018

antonybudianto commented Apr 12, 2018

CyanDeveloper commented Feb 22, 2019

davidtlee commented Feb 23, 2019

timscullin commented Feb 23, 2019

long1eu commented Feb 23, 2019 via email

efraespada commented Feb 24, 2019

permadiberlianto commented Mar 6, 2019

CyrusZei commented Mar 7, 2019

jeremyfrancis commented Mar 7, 2019

sam26880 commented Mar 7, 2019

devpato commented Mar 10, 2019

Kinark commented Mar 11, 2019

thirajade commented Mar 13, 2019

awtmarriott commented Mar 20, 2019

devpato commented Mar 21, 2019

rockwotj commented Mar 21, 2019 • edited Loading

davidtlee commented Mar 21, 2019 via email

rockwotj commented Mar 21, 2019 • edited Loading

mesqueeb commented Mar 22, 2019

jeremyfrancis commented Mar 22, 2019

darren-dev commented Mar 29, 2019

artolshansky commented Mar 29, 2019 • edited Loading

jamesreilly5 commented Mar 30, 2019

jamesreilly5 commented Apr 4, 2019

darren-dev commented Apr 5, 2019 • edited Loading

jamesreilly5 commented Apr 13, 2019

mpivchev commented May 7, 2019

wilhuff commented May 7, 2019

EpicButterz commented Nov 16, 2017 •

edited by jshcrowthe

Loading

EpicButterz commented Nov 16, 2017 •

edited

Loading

mdrideout commented Mar 11, 2018 •

edited

Loading

csoreff commented Apr 12, 2018 •

edited

Loading

antonybudianto commented Apr 12, 2018 •

edited

Loading

antonybudianto commented Apr 12, 2018 •

edited

Loading

rockwotj commented Mar 21, 2019 •

edited

Loading

rockwotj commented Mar 21, 2019 •

edited

Loading

artolshansky commented Mar 29, 2019 •

edited

Loading

darren-dev commented Apr 5, 2019 •

edited

Loading