CODEWITHSUNDEEP
javascriptgoogle-cloud-firestore
Abraham
Abraham

Reputation: 15690

index required for firestore fields to use orderBy in query

I was building a simple query that includes ordering at the end.

But firestore is asking me to create index that combines the queried field and the orderBy field. And it is going to be many indexes that will be created for this that combine the order field with the rest fields (or even combination of fields).

I have 4 fields to filter results by, and need to be ordered by another field for which I have to create 24 indexes for the different combinations, including both ascending and descending for order. This way if I have tens of fields? I think I am going to have to create hundreds of indexes.

So is it a good practice to get on and create all those numerous indexes of combination of fields combined with the order by field, even for both directions ASC and DESC? would it create issues if I proceed with that? or what pattern is fit for this simple task way to do this?

My query is

var query = ref.collection("data/devices");

if (params.region) query = query.where("region", "==", params.region);
if (params.color) query = query.where("color", "==", params.color);
query = query.orderBy("price", "ASC ");

in my test, only params.color wasn't included since it was undefined. So the query only had .where("region") and orderBy price

And it generated this error Error: 9 FAILED_PRECONDITION: The query requires an index. You can create it here: [Link to create the index]

Upvotes: 0

Views: 1168

Answers (1)

maganap
maganap

Reputation: 2894

To handle the query you're showing:

var query = ref.collection("data/devices");

if (params.region) query = query.where("region", "==", params.region);
if (params.color) query = query.where("color", "==", params.color);
query = query.orderBy("price", "ASC");

You need to create 2 composite indexes:

  • region ASC, price ASC
  • color ASC, price ASC

Those 2 separate indexes will be merged and will be able to handle the combination you need, even when using both region and/or color. For example:

db.collection('whatever')
  .where('region', '==', 'A')
  .where('color', '==', 'B')
  .where('prop3', '==', 'C')
  .where('prop4', '==', 'D')
  .orderBy('price', 'ASC');

If that query is exactly what you will always use, you can create a single composite index with the 5 fields. But, if the "where" clauses are optional, then you'd need to create 4 different composite indexes:

  • region ASC, price ASC
  • color ASC, price ASC
  • prop3 ASC, price ASC
  • prop4 ASC, price ASC

You also get the benefit of having different combinations available, like:

.where('region', '==', 'A').where('prop4', '==', 'D').orderBy('price', 'ASC');

for example.

This is called Index Merging and is well explained here.

Edit: Notice that:

  • Index merging only works with equality comparisons (.where('x', '==', 5)) and not range comparisons (.where('x', '<', 5)).
  • If you want to sort by price,DESC instead of ASC you will have to create a different set of indexes as explained here.

Upvotes: 3

Related Questions