CODEWITHSUNDEEP
firebasegoogle-cloud-firestore
Thijs Koerselman
Thijs Koerselman

Reputation: 23271

How to query a variable field and apply ordering without creating an index first?

Edit: I've simplified the question and examples because this problem only applies once you start using orderBy.

I have a user collection where each user is subscribed to a number of variables like this:

user {
    var1: true,
    var2: true,
    var2: true,
    metric: 10
}

I do this because I need to query something like: 

async function getUsersForVar(varId) {
  const snapshot = await db.collection('users')
    .where(varId, '==', true)
    .get()
  ...
}

This works fine, until I need to select a group of users based on the metric:

async function getTopUsersForVar(varId) {
  const snapshot = await db.collection('users')
    .where(varId, '==', true)
    .orderBy('metric')
    .limit(100)
    .get()
  ...
}

When you do this, Firestore will tell you it needs to create an index specific to the used varId plus the metric. But because the items are variable, I can't know or index every combination upfront.

This document describes how you can encode the property for sorting into the key values, but for me that is not an option, because the metric changes over time.

Is this just not possible?

Maybe it is simply too much to expect the DB to be able to execute a query like this efficiently if the metric changes.

The number of user documents returned from the query will be in the thousands. Is there anything I can do to prevent querying all data and sorting client-side?

AFAIK Firestore will fetch the full document for each query. This query will be executed multiple times a minute, so if I'm fetching thousands of user documents every time I execute this query there will be a lot of overhead.

If there is no other way, I should probably isolate all of the relevant data from user into a separate model / root collection, so that not all other user data is fetched to make this selection.

Would this be any different when using the Firebase Realtime DB?

Upvotes: 4

Views: 1614

Answers (2)

nshmura
nshmura

Reputation: 6000

This document describes about similar issues.

As described above document, you can work around by storing metric values like below:

user {
  var1: {
      metric1: 10,
      metric2: 1502144665
  }
  var2: {
      metric1: 10,
      metric2: 1502144665
  }
  var3: {
      metric1: 10,
      metric2: 1502144665
  }
  metric1: 10
  metric2: 1502144665
}

Now you can query without any custom indexes, like below:

const column = `${varId}.${metric}`;
const snapshot = await db.collection("users")
    .where(column, ">", 0) // check dose user subscribe varId 
    .orderBy(column)
    .limit(100)
    .get()

Upvotes: 1

Todd Kerpelman
Todd Kerpelman

Reputation: 17523

So here's the deal:

If you want to do a query -- inequality or equality -- on a single field, you don't need an index. (Even if that field is a property in a map field, like your top example.)

If you want to do an equality query across multiple fields, then you also don't need a custom index.

If you want to do a query that consists of 1 or more equality searches, followed by either an inequality search or an "order by" on a different field, then you do need a custom index. And you are currently limited to 125 different custom indexes on the same database.

So, with that in mind, I'm not sure it's possible for you to do exactly the kind of query you're looking for without creating custom indexes for each one.

One workaround would be to run the query without ordering the results, and then order them on the client.

Or as nshmura suggested, another workaround you can try is to add the "metric" value to the variable you're querying for. So rather than structuring your data like:

user {
    var1: true,
    var2: true,
    var3: true,
    metric: 10
}

You might consider structuring your data like: 

user {
    var1: 10,
    var2: 10,
    var3: 10,
    metric: 10
}

Then you could perform a search like

 const snapshot = await db.collection('users')
    .where(varId, '>', 0)
    .limit(100)
    .get()

And it will automatically be ordered by that value. Whether or not that actually works probably depends a lot on your specific use case.

Upvotes: 2

Related Questions