Firestore Flutter select where not equals and where not in [duplicate]

Question

Is it possible to in flutter firestore

1. Select where not equals? Something similar to SQL: SELECT * FROM tbl WHERE id != 2
2. Select where not in? Something similar to SQL: SELECT * FROM tbl WHERE id NOT IN [1,3,6]

On firebase documentation page, they have in and array-contains-any but no examples are shown for flutter.

https://firebase.google.com/docs/firestore/query-data/queries

Answer 1

Queries with a != clause are not supported. In this case, split the query into a greater-than query and a less-than query. For example, although the query clause where("age", "!=", "30") is not supported, you can get the same result set by combining two queries, one with the clause where("age", "<", "30") and one with the clause where("age", ">", 30).

Firestore does not support a direct equivalent of !=. The supported query operators are <, <=, ==, >, or >= so there's no "whereNotEqual".

You can test if a field exists at all, because all filters and order bys implicitly create a filter on whether or not a field exists. For example, in the Android SDK:

collection.orderBy("name")

would return only those rows that contain a "name" field.

As with explicit comparison there's no way to invert this query to return those rows where a value does not exist.

There are a few work-arounds. The most direct replacement is to explicitly store null then query collection.whereEqualTo("name", null). This is somewhat annoying though because if you don't populate this from the outset you have to backfill existing data once you want to do this. If you can't upgrade all your clients you'll need to deploy a function to keep this field populated.

Another possibility is to observe that usually missing fields indicate that a document is only partially assembled perhaps because it goes through some state machine or is a sort of union of two non-overlapping types. If you explicitly record the state or type as a discriminant you can query on that rather than field non-presence. This works really well when there are only two states/types but gets messy if there are many states.

source: Cloud Firestore whereNotEqual