user2896438
user2896438

Reputation: 944

MongoDB collection as a lookup table with range of values

I have an interesting scenario that I can't find a clean solution for.

Let's suppose I have a chocolate factory that gets a Grade[A-Z] score based on how many chocolate bars it produces. The Grades to numChocolateBars mapping is stored in a collection as follows:

Grade, numChocolateBars, neededChocolateForNextGrade
Z, 0, 100
Y, 100, 150
X, 250, 1100
...
B, 11805606, 1228825
A, 13034431,

Examples:

Using a Mongoose or MongoDB query, what's the best way to find the appropriate grade for a given numChocolateBars?

My current solution

I've managed to get things working using:

GradeMap.findOne(
  {
    numChocolateBars: { $gt: thisMonthsNumChocolate}
  }
).Grade-1;

Which essentially finds the first record with numChocolateBars > thisMonthsNumChocolate (one grade higher than I want) and subtracts one from it. This works as long as I have a final dummy record with numChocolteBars higher than any possible value.But this seems very unstable, and it relies on the order in which Mongo searches through the data. I'm worrie this order might change based on indexing or other factors. Is there a better way?

Upvotes: 1

Views: 225

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

You can try below query :

db.collection.find({
  numChocolateBars: {
    $lte: thisMonthsNumChocolate
  },
  neededChocolateForNextGrade: {
    $gt: thisMonthsNumChocolate
  }
})

Test : MongoDB-Playground

Edit :

As neededChocolateForNextGrade is not a fixed value & if it depends on numChocolateBars :

db.collection.find({
  numChocolateBars: {
    $lte: 149
  },
  $expr: {
    $gt: [
      {
        $sum: [
          "$numChocolateBars",
          "$neededChocolateForNextGrade"
        ]
      },
      149
    ]
  }
})

Test : MongoDB-Playground

Upvotes: 1

Related Questions