CODEWITHSUNDEEP
mongodb
Jammer
Jammer

Reputation: 61

MongoDB query for count based on some value in other collection

I have a configuration collection with below fields:

1) Model

2) Threshold

In above collection, certain threshold value is given for every model like as follow:

'model1' 200

'model2' 400

'model3' 600

There is another collection named customer with following fields:

1)model

2)customerID

3)baseValue

In above collection, data is as follow:

'model1' 'BIXPTL098' 300

'model2' 'BIXPTL448' 350

'model3' 'BIXPTL338' 500

Now I need to get the count of customer records which have baseValue for that particular model greater than the threshold of that particular model in configuration collection.

Example : For the above demo data, 1 should be returned by the query as there is only one customer(BIXPTL098) with baseValue(300) greater than Threshold(200) for that particular model(model1) in configuration

There are thousands of records in configuration collection. Any help is appreciated.

Upvotes: 0

Views: 451

Answers (1)

wdberkeley
wdberkeley

Reputation: 11671

How often does the threshold change? If it doesn't change very often, I'd store the difference between the model threshold and the customer baseValue on each document.

{
    "model" : "model1",
    "customerID" : "BIXPTL098",
    "baseValue" : 300,
    "delta" : 100    // customer baseValue - model1 threshold  = 300 - 200 = 100
{

and query for delta > 0

db.customers.find({ "delta" : { "$gt" : 0 } })

If the threshold changes frequently, the easiest option would be to compute customer documents exceeding their model threshold on a model-by-model basis:

> var mt = db.models.findOne({ "model" : "model1" }).threshold
> db.customers.find({ "model" : "model1", "baseValue" : { "$gt" : mt } })

Upvotes: 1

Related Questions