FaunaDB search document and get its ranking based on a score

Question

I have the following Collection of documents with structure:

type Streak struct {
    UserID    string    `fauna:"user_id"`
    Username  string    `fauna:"username"`
    Count     int       `fauna:"count"`
    UpdatedAt time.Time `fauna:"updated_at"`
    CreatedAt time.Time `fauna:"created_at"`
}

This looks like the following in FaunaDB Collections:

{
  "ref": Ref(Collection("streaks"), "288597420809388544"),
  "ts": 1611486798180000,
  "data": {
    "count": 1,
    "updated_at": Time("2021-01-24T11:13:17.859483176Z"),
    "user_id": "276989300",
    "username": "yodanparry"
  }
}

Basically I need a lambda or a function that takes in a user_id and spits out its rank within the collection. rank is simply sorted by the count field. For example, let's say I have the following documents (I ignored other fields for simplicity):

user_id	count
abc	12
xyz	10
fgh	999

If I throw in fgh as an input for this lambda function, I want it to spit out 1 (or 0 if you start counting from 0).

I already have an index for user_id so I can query and match a document reference from this index. I also have an index sorted_count that sorts document based on count field ascendingly.

My current solution was to query all documents by sorted_count index, then get the rank by iterating through the array. I think there should be a better solution for this. I'm just not seeing it.

Please help. Thank you!

user_4461248 · Answer

Counting things in Fauna isn't as easy as one might expect. But you might still be able to do something more efficient than you describe.

Assuming you have:

CreateIndex(
  {
    name: "sorted_count",
    source: Collection("streaks"),
    values: [
      { field: ["data", "count"] }
    ]
  }
)

Then you can query this index like so:

Count(
  Paginate(
    Match(Index("sorted_count")),
    { after: 10, size: 100000 }
  )
)

Which will return an object like this one:

{
  before: [10],
  data: [123]
}

Which tells you that there are 123 documents with count >= 10, which I think is what you want.

This means that, in order to get a user's rank based on their user_id, you'll need to implement this two-step process:

Determine the count of the user in question using your index on user_id.
Query sorted_count using the user's count as described above.

Note that, in case your collection has more than 100,000 documents, you'll need your Go code to iterate through all the pages based on the returned object's after field. 100,000 is Fauna's maximum allowed page size. See the Fauna docs on pagination for details.

Also note that this might not reflect whatever your desired logic is for resolving ties.

FaunaDB search document and get its ranking based on a score

1 Answers