Firestore query order on field with filter on a different field

Question

I have a problem with query conditions in Google Cloud Firestore.

Anyone can help me.

Below is my code to get the first Document to start with HA_ and order by ID DESC

public Article getLastArticleProvider() {
    ApiFuture<QuerySnapshot> query = firebaseDB.getTblArticles()
            .whereGreaterThanOrEqualTo("articleId", "HA_")
            .orderBy("id", Query.Direction.DESCENDING)
            .limit(1)
            .get();

    QuerySnapshot snapshotApiFuture;
    Article article = null;

    try {
        snapshotApiFuture = query.get();
        List<QueryDocumentSnapshot> documents = snapshotApiFuture.getDocuments();
        for (QueryDocumentSnapshot document : documents) {
            article = document.toObject(Article.class);
        }
    } catch (InterruptedException | ExecutionException e) {
        return null;
    }
    return article;
}

I want to get the last id of article with articleId start with "HA_" or "XE_"

Ex for above image:

• if(articleId start with "HA_") => return object of id 831
• if(articleId start with "XE_") => return object of id 833

Now i get an error

java.util.concurrent.ExecutionException: com.google.api.gax.rpc.InvalidArgumentException: io.grpc.StatusRuntimeException: INVALID_ARGUMENT: inequality filter property and first sort order must be the same: articleId and id

Answer 1

TL;DR

Add a dummy order right after your inequality filter property and change your query priority to fit your desires.

I encountered the same issue on javascript but I believe it's also a java solution.

Full Solution:

When I run my query:

module.exports.getLastGroupChat = async function (groupId) {
    let colRef = db.collection('messages')
    colRef = colRef.where('groupId', '==', groupId)
    colRef = colRef.where('userId', '!=', '')
    colRef = colRef.orderBy('timestamp', 'desc').limit(1)
    const doc = await colRef.get()
    return doc

}

And received:

inequality filter property and first sort order must be the same: userId and timestamp

To solve that issue, first of all, I had to add a sort order, of the same inequality property, right after my inequality filter.

In addition, I had to change my query priority to achieve a dummy sort order of the inequality property.

Note: You can run where -> order -> where -> order on the same query!

module.exports.getLastGroupChat = async function (groupId) {
    let colRef = db.collection('messages')
    colRef = colRef.where('userId', '!=', '')
    colRef = colRef.orderBy('userId', 'desc')
    colRef = colRef.where('groupId', '==', groupId)
    colRef = colRef.orderBy('timestamp', 'desc').limit(1)
    const doc = await colRef.get()
    return doc
}

That query worked perfectly on my local debug firestore. Push your changes to your firebase cloud functions and trigger your function. Check out your function logs, You may get an indexing error.

The query requires an index. You can create it here: https://console.firebase.google.com/v1/r/project...

Make sure you get into the link and build the index. It will take about five to ten minutes to take effect. Then run again you function and everything should be just fine.

Have fun! :)

Answer 2

perhaps firebase has changed things since this question was asked.

The answer is that you CAN do a filter and an orderby on different fields.

you can chain orderby's and filters, however, if you do filter first, you then have to order by that filter before you can orderby any other field. e.g.

citiesRef.where('population', '>', 2500000).orderBy('population').orderBy('country');

It's in their docs. https://firebase.google.com/docs/firestore/query-data/order-limit-data#order_and_limit_data

However, if you have a filter with a range comparison (<, <=, >, >=), your first ordering must be on the same field, see the list of orderBy() limitations below.

you can chain orderBys of different fields, I just did it on one of my queries. (you may get an error the first time you run it, it asks you to create an index, it even has a link to create the index)

await db.collection('daily_equipments').where('date', '<', tomorrow._d).where('date', '>=', date).orderBy('date').orderBy('order','asc').get();

Answer 3

As the following error says:

inequality filter property and first sort order must be the same: articleId and id

So you cannot filter your elements and sort them at the same time, using different properties, in your case articleId and id.

There is also an example of how not to do it in the official documentation:

• Range filter and first orderBy on different fields

   citiesRef.whereGreaterThan("population", 100000).orderBy("country"); //Invalid
  

So to solve this, you should filter and order on the same document property.