Firestore with complex filter and composite indexes

Question

I use Cloud Firestore and this is my data structure :

   Firestore-root
   |
   --- collection (collection)
         |
         --- uid (document)
              |
              --- name: some_name
              |
              --- some_fields: some_data
              |
              --- origin: [origin_1, origine_2, ...] <- List
              |
              --- category: [category_1, category_2, ...] <- List
              |
              --- meals: [meals_1, meals_2, ...] <- List
              |
              --- some_other_filters: [.....] <- List
              |
              --- all_categories: [origin_1:true, origine_2:true, meals_1:true, ....]

I created the Map field all_categories to hold all different filters (origin, category, ...) so that I can perform queries easily like: if user select meals_4 and category_2, category_6 and some_other_filter I can do:

List<String> filters = getFilters(); 

query = recipeRepository.getUsersRecipeCollection()
            .orderBy(sorted_filter, DESCENDING).limit(5);

for (String item: filters)
    query = query.whereEqualTo("all_categories."+item, true);

query.get().addOnCompleteListener(...)

I cannot use the whereArrayContainsAny function since I need to get the exact items based on selected filters. What I need is something like "whereArrayContainsExactly.."

So, for example, if a user select 1, 2,.. or 25 different items from my filter page (like: category_1, meals_2, origin_1,... ), does it mean I need to create all 25 combinations of the composite indexes (through the link I get from the console) ? or I create 25 indexes of that map fields?

Second attempt:

I denormalised the data as following:

The collection that hold all the data:

The filter collections:

If I select meals_1 and origin_1, With whenAllSuccess I can get all document ids like the following:

Iterator<String> iterator = filters.iterator();
        List<Task> tasks = new ArrayList<>();
        while (iterator.hasNext()) {
            String filter = iterator.next();
            tasks.add(recipeRepository.getFilterCollection("filter_"+filter).get());
        }

        Tasks.whenAllSuccess(tasks.toArray(new Task[tasks.size()]))
                .addOnSuccessListener(new OnSuccessListener<List<Object>>() {
                    @Override
                    public void onSuccess(List<Object> objects) {
                        if (!objects.isEmpty()) {

                            for (Object querySnapshot: objects) {
                                QuerySnapshot querySnap = (QuerySnapshot)querySnapshot;

                                for (DocumentSnapshot id: querySnap.getDocuments()) {
                                    doc_ids.add(id.getId());
                                }
                            }

                            CollectionReference collectionReference = recipeRepository.getUsersRecipeCollection();

                            List<DocumentReference> listDocRef = new ArrayList<>();
                            for (String id: doc_ids) {
                                DocumentReference docRef = collectionReference.document(id);
                                listDocRef.add(docRef);
                            }

                            List<Task<DocumentSnapshot>> tasks = new ArrayList<>();
                            for (DocumentReference documentReference : listDocRef) {
                                Task<DocumentSnapshot> documentSnapshotTask = documentReference.get();
                                tasks.add(documentSnapshotTask);
                            }

                            Tasks.whenAllSuccess(tasks).addOnSuccessListener(new OnSuccessListener<List<Object>>() {
                                @Override
                                public void onSuccess(List<Object> list) {
                                    Log.e(TAG, ">> list objects: " + list.size() + " data: " + list);
                                    for (Object object : list) {
                                        Recipes recipes = ((DocumentSnapshot) object).toObject(Recipes.class);

                                        if (recipes == null) continue;

                                        Log.e(TAG, ">> recipe name: " + recipes.getName());
                                    }
                                }
                            });
                        }
                    }
                });

With this I get all data containing meals_1 OR origin_1 (same result as using whereArrayContainsAny).

What I need is to get the list of docs ids that exists in both meals_1 and origin_1 collections.

Answer 1

So, for example, if a user select 1, 2,.. or 25 different items from my filter page (like: category_1, meals_2, origin_1,... ), does it mean I need to create all 25 combinations of the composite indexes (through the link I get from the console) ? or I create 25 indexes of that map fields?

As I see in the for loop, at each iteration you are creating a new Query object, and this is because Cloud Firestore queries are immutable. Because you are using in the same query a .orderBy() call along with a .whereEqualTo() call, an index is required.

And to answer your question, yes, an index is required for each and very different query that you perform.

Edit:

An alternative solution might be to denormalize the data, by adding separate collections of each type of meal, and every time a filter is added you should create a new query for the selected collection. This is a common practice when it comes to NoSQL databases.