Flutter Firestore Query Indexing

Question

I have a problem. I would like to use where and order by within a Firestore query. Here you can see the query:

  Stream<List<Exercise>> getExercises(){
return _firestore
    .collection('${_authentication.getUID()}')
    .where('typ', isEqualTo: 'exercise' )
    .orderBy('timestamp', descending: true)
    .snapshots()
    .map((snapshot) => snapshot.docs
    .map((doc) => Exercise.fromJson(doc.data()))
    .toList());

}

The following error message occurs:

[VERBOSE-2:ui_dart_state.cc(186)] Unhandled Exception: [cloud_firestore/failed-precondition] Operation was rejected because the system is not in a state required for the operation's execution. If performing a query, ensure it has been indexed via the Firebase console.

Usually you get a link to create the index. Now the question is, where do I get this link from. I have never done the manual creation of the index in the Firestore console.

Thanks in advance

Solution

Your query has dynamic collection IDs i.e. UID of users. You will have to create an index for each of those which is not ideal. I'd recommend refactoring your structure as follows:

1. A single root level collection called "users".
2. A document for each user in "users" collection with user's UID as the document key.
3. A sub-collection called "tasks" (or whatever data you are storing) and a dedicated document for each task in that sub-collection.

Now the database structure would look something like:

users -> {userID} -> tasks -> {taskID}
(col)     (doc)      (col)     (doc)

Now you won't have to create an index for each collection.

You can query user's exercise by this query:

return _firestore
    .collection('users')
    .doc('${_authentication.getUID()}')
    .collection('tasks')
    .where('typ', isEqualTo: 'exercise' )
    .orderBy('timestamp', descending: true)
    .snapshots()
    .map((snapshot) => snapshot.docs
    .map((doc) => Exercise.fromJson(doc.data()))
    .toList());

Try creating an index manually in the console and running your query. If you wish to create an index using the click to create index link, then you would have to use functions emulator and run your query locally using Admin SDK as the client SDKs are not returning the index creation link in errors (known issue). You can refer to this answer for detailed explanation for getting the link using functions emulator.

Comments

[Dharmaraj]

There's an issue that client SDKs are not returning index creation URLs. You need to create it manually or use the Firebase Functions Emulator to get it using Admin SDK as it still returns the URL. Checkout this answer which explains how to get that link by running functions locally.

[LazyTurtle]

I think the problem is, that I use the User ID as collection, that makes it difficult for me to do it manually

[Dharmaraj]

That's correct. Ideally you should have a static collection name.

[LazyTurtle]

Cant change that anymore :/

[Dharmaraj]

I'm not sure what you use case is but ideally something like: users > {userID} > tasks > {taskID} where tasks will contain all documents about that user (exercise and stuff) is common strucutre :') So now you only have a users collection and tasks sub-collection to index.

[LazyTurtle]

Yeah I have a collection for each user and in this collection everything known about the user is stored

[LazyTurtle]

Is there a possible way to add the index for my structure ?