Using Where and Order by different fields in Firestore query

Question

I have a Firestore collection named channels, and I'd like to get the list of channels based on an array of IDs and order it by the createdAt field, this is my function :

  const getChannels = () => {
    const q = query(
      collection(db, "channels"),
      where(documentId(), "in", [
        "F0mnR5rNdhwSLPZ57pTP",
        "G8p6TWSopLN4dNHJLH8d",
        "wMWMlJwa3m3lYINNjCLT",
      ]),
      orderBy("createdAt")
    );
    const unsubscribe = onSnapshot(q, (snapshot) => {
      snapshot.docs.map((doc) => {
        console.log(doc.data());
      });
    });
    return unsubscribe;
  };

But I'm getting this error

FirebaseError: inequality filter property and first sort order must be the same: __name__ and createdAt.

It only works if I orderBy documentId().

I'm aware there is a limitation in the docs about this, but I'm wondering if there is a workaround for this type of situation.

Also the answer for this question isn't working anymore I guess.

Answer 1

The title of your question indicates that you are trying to use where and orderBy for different fields. But note that you are using documentId() in the where condition to filter, which is not a field in the Firestore document.

So if you filter is based on documentId(), you can use only documentId() in orderBy() clause, that also in ascending order because currently Firestore does not support sorting in descending order of documentId() which is mentioned in this answer.

Let’s take a look at the following examples -

const data=await db.collection("users").where(admin.firestore.FieldPath.documentId(),"in",["104","102","101"]).orderBy(admin.firestore.FieldPath.documentId()).get();

The above will work and sort the documents based on documentId() after filtering based on documentId(). But it is not relevant to apply an orderBy() clause based on the documentId(), because without applying the orderBy() clause also yields the same result as, by default, Firestore query gives documents in ascending order of documentId(). That means the following also yields the same result -

const data=await db.collection("users").where(admin.firestore.FieldPath.documentId(),"in",["104","102","101"]).get();

Now Firestore doesn’t support to sort in descending order of documentId() which means the following will not work -

const data=await db.collection("users").where(admin.firestore.FieldPath.documentId(),"in",["104","102","101"]).orderBy(admin.firestore.FieldPath.documentId(),"desc").get();

This will ask to create an index -

The query requires an index. You can create it here:

But if you go there to create an index it will say -

__name__ only indexes are not supported.

Now let's come to your query. What you are trying to do is to filter based on documentId() and then orderBy() based on createdAt field which is not possible and it will give the following error-

inequality filter property and first sort order must be the same.

You may think to use two orderBy() clauses, something like this -

const data=await db.collection("users").where(admin.firestore.FieldPath.documentId(),"in",["104","102","101"]).orderBy(admin.firestore.FieldPath.documentId()).orderBy(“createdAt”
).get();

Which will not work and give the following error

order by clause cannot contain more fields after the key

I am not sure of your use case but it’s not a great idea to filter based on documentId(). If it is required to filter based on documentId(), I would suggest creating a field in the Firestore document which will contain the documentIds and filter based on that.

Now considering the title of the question, yes it is possible to use where() and orderBy() clauses for different fields in Firestore. There are some limitations and you need to stick to that -

If you include a filter with a range comparison (<, <=, >, >=), your first ordering must be on the same field.

const data=await db.collection("users").where(“number”,">=", “101”).orderBy(“createdAt”).get(); 

The above query doesn't work.

const data=await db.collection("users").where(“number”,">=", “101”).orderBy(“number”).get(); 

The above query works and you can still use further orderBy() on different fields, something like following -

const data=await db.collection("users").where(“number”,">=", “101”).orderBy(“number”).orderBy(“createdAt”).get(); 

You cannot order your query by any field included in an equality (=) or in clause.

const data=await db.collection("users").where(“number”,"in",["104","102","101"]).orderBy(“number”).get();
const data=await db.collection("users").where(“number”,"==", “101”).orderBy(“number”).get(); 

The above two don’t work.

Answer 2

Firestore's speed and efficiency comes almost ENTIRELY from it's use of indexes. Inequalities (INCLUDING in and not-in) are accomplished by sorting by the index, and using the value as a "cut-off" - thus REQUIRING (whether you want it or not) the orderby() to be on the same field as the inequality.

The "answer not working anymore" was never really working in the first place, as the above shows. If you aren't trying to paginate, do the obvious and "filter" by the document ID's and sort on the client.

BUT...

...more importantly, it is ALMOST NEVER useful nor performant to use documentId's to select from the database, unless you both copy it to a field, AND are looking for a SPECIFIC id. In almost all cases, it would be FAR better to use a query on another field (however you got the list of documentId's in the first place), then orderBy. Yes, the inequality/orderBy is a limitation, but it's there for a reason.

Going forward, an important design decision is to understand what questions you want your data to answer, and design your entire database schema to support those queries - this is the fundamental nature of NoSQL.

Answer 3

Problem:The other link that you have shared before perfectly works and the only solutions available is to create an index. However the reason you are not able to do a where and order with the above example is because you cannot create an index with the document id and createdAt.

Solution: To do so add the document id as one of the field say docID in the document then create an index with the fields docID and createdAt. This should be working for you.

Note: I have not physically tested this. Will update once I have checked it