1

We have a collection, let's say, called "restaurants". For each restaurant, there's a subcollection called "visited", where we capture the userId of each user who has visited that restaurant. Here's how the database looks like:

enter image description here

We picked creating a subcollection over using an array inside of restaurants because we expect this number to increase up to very large document size. Besides, we are not sure about the performance of queries involving really long arrays (100s of 1000s of them).

Anyway, so now that we have these sub-collections called "visited", we are able to use collection group queries with indexes to do something like "find all restaurants visited by user: "abcdxyz" with a "==" query, and in a certain order lets say, sorted by the visitedOn date/time.

Here's what we tried running in python:

db.collection_group(u'visited').where(u'userId', u'!=', u'idoftheuser').order_by('visitedOn', direction=firestore.Query.DESCENDING)

When we run this to find restaurants that were never visited by this user, Firestore is throwing the following error:

ValueError: Operator string '!=' is invalid. Valid choices are: <, <=, ==, >, >=, array_contains

Is this true? Why would they not allow "!=" queries when they are allowed in individual collections? How can we get around this issue? We really don't want to make any changes to our database model anymore. Too much back and forth already.

Any suggestions?

Alex Mamo
  • 130,605
  • 17
  • 163
  • 193
Abhishek Sharma
  • 609
  • 1
  • 11
  • 22

1 Answers1

1

When it comes to not equal (!=) queries, there are some limitations:

In a compound query, range (<, <=, >, >=) and not equals (!=, not-in) comparisons must all filter on the same field.

That's the reason why the following query:

db.collection_group(u'visited').where(u'userId', u'!=', u'idoftheuser').order_by('visitedOn', direction=firestore.Query.DESCENDING)

Doesn't work. Besides that, please also note that a query that contains a where() and an order_by() call, requires an index to be created.

Alex Mamo
  • 130,605
  • 17
  • 163
  • 193
  • Okay. But the limitations don't talk about the query not working, does it? I don't care about the order. Will it work then? And yes, I have already created an index and that's how my == query works. If the above doesn't work even after removing "order_by", is there an alternative? – Abhishek Sharma Apr 04 '21 at 14:23
  • As a matter of fact, I just tried '!=' in Java and the query at least went through (for some reason it didn't work in Python). But, the query result was not promising - it did not return a document from the collection where there was at least one != condition that succeeded. Very strange. – Abhishek Sharma Apr 04 '21 at 14:27
  • The limitation itself provides enough information, "comparisons must all filter on the same field", otherwise will **not** work. Besides that, it's not about the order, each method call returns a Query object, so the order doesn't matter. An alternative, don't filter elements on different fields and always create an index, or create another schema in which you can store elements as needed, even if it comes to [denormalization](https://stackoverflow.com/questions/50947856/firebase-how-to-retrieve-two-values-for-the-same-key-query/50948838). – Alex Mamo Apr 04 '21 at 15:24
  • Alex, I am not sure what you mean by "don't filter elements on different fields", there's only one field being used in the query -> where('userId', '!=', 'idoftheuser') – Abhishek Sharma Apr 05 '21 at 05:58
  • It's the opposite of "comparisons must all filter on the same field". That's something you don't do. – Alex Mamo Apr 05 '21 at 09:03
  • Hi Alex, honestly, I felt like you didn't want to give out details. Your last response was as if you are mocking my question. Sorry I felt that way, but I would have appreciated an example or two. By saying, "It's the opposite of "comparisons must all filter on the same field", it added no value. This was after I had clearly asked with the exact query in the comment right before. So no, I can't accept your answer in its current state. But thanks for trying. – Abhishek Sharma Apr 13 '21 at 08:29