Combining mongoDB collections to show on one page

In my current project, I’m displaying a country’s corruption trend from from Transparency International alongside Kiva loans. Since both of these data sets have different refresh intervals and sources, I do a mongoImport to get them into the database.

Example records:

The data is pretty clean at the get-go with a logical join on the location.country field from loans with the Country field from the corruption index.

Doing this in nodeJS isn’t so simple though.

First, we have to define the models:

For the corruption index model, I have:

var mongoose = require('mongoose'),
Schema = mongoose.Schema,
path = require('path');
var corruptSchema = new Schema({
corrupts: {
Country : {
type: 'String'
},
ISO3: {
type: 'String'
},
Region: {
type: 'String'
},
CPI_score_2017: {
type: 'Number'
},
CPI_score_2016: {
type: 'Number'
},
CPI_score_2015: {
type: 'Number'
},
CPI_score_2014: {
type: 'Number'
},
CPI_score_2013: {
type: 'Number'
},
CPI_score_2012: {
type: 'Number'
}
}
});
module.exports = mongoose.model('corrupt', corruptSchema);

view raw
corruption-index.js
hosted with ❤ by GitHub

The loan model is a bit different in 4 ways:

  1. It requires the corruption index for reference as part of the loan object (line 4)
  2. It defines a virtual object (line 107) – including which fields to join on
  3. It makes sure the virtual objects are populated for each find against the loan collection (line 114)
  4. It allows the populated virtual data to be accessible to toObject & toJSON nodeJS calls (line 102/103).

Here’s the full code:

var mongoose = require('mongoose'),
Schema = mongoose.Schema,
path = require('path'),
Cindex =require('./cindex');
mongoose.set('debug', true); // shows calls against mongoDB. Should be turned off in prod.
var loanSchema = new Schema({
loans: {
id: {
type: 'Number'
},
name: {
type: 'String'
},
description: {
languages: {
type: [
'String'
]
}
},
status: {
type: 'String'
},
funded_amount: {
type: 'Number'
},
basket_amount: {
type: 'Number'
},
image: {
id: {
type: 'Number'
},
template_id: {
type: 'Number'
}
},
activity: {
type: 'String'
},
sector: {
type: 'String'
},
themes: {
type: [
'String'
]
},
use: {
type: 'String'
},
location: {
country_code: {
type: 'String'
},
country: {
type: 'String'
},
town: {
type: 'String'
},
geo: {
level: {
type: 'String'
},
pairs: {
type: 'String'
},
type: {
type: 'String'
}
}
},
partner_id: {
type: 'Number'
},
posted_date: {
type: 'Date'
},
planned_expiration_date: {
type: 'Date'
},
loan_amount: {
type: 'Number'
},
borrower_count: {
type: 'Number'
},
lender_count: {
type: 'Number'
},
bonus_credit_eligibility: {
type: 'Boolean'
},
tags: {
type: 'Array'
}
}
}, {
toJSON: {virtuals:true}, //allows virtual attributes to show up when doing toJSON calls
toObject:{virtuals:true} // allows virtuals to show up when doing toObject calls
});
//definition of virtual object
loanSchema.virtual('corruption', {
ref:'corrupt', //name of the model exported
localField: 'location.country', //what from loans collection to join on
foreignField: 'Country', //what from corrupt collection to join on
justOne: true
});
loanSchema.pre('find',function() {
this.populate('corruption');
});
module.exports = mongoose.model('Loans', loanSchema);

view raw
loan-model.js
hosted with ❤ by GitHub

Next, verify your controller code

Now for the controller, there is no need to call populate or attempt to merge the collections together. That logic stays in the model which is where we want it.

I learned through trial & error that toObject in this view was needed so that the view can see the dynamic data. 

LoanModel.find({},{},{limit: 4, sort:{timestamp:1}}).exec(function(error, loans) {
if(error){throw error;}
for (var i = 0; i<loans.length; i++) {
viewModel.loans[i]=loans[i].toObject();
}
res.render('index',viewModel);
});

view raw
controller.js
hosted with ❤ by GitHub

Finally, we have the view bring it all together

Some learnings out of this (and I’m still iterating on look/feel):

  1. The handlebars #if, else, /if is going to give me nightmares.
  2. Bootstrap rocks. Makes it easy to iterate on the the right presentation with highly descriptive examples.
<div class="row">
{{#each loans}}
<div class="card" style="width: 18rem; height: 36rem">
<a class="card-link" href="https://www.kiva.org/lend/{{id}}">
<img class="card-img-top" width="288" height="288" src="https://www.kiva.org/img/288×288/{{image.id}}.jpg}">
</a>
<div class="card-body">
<ul class="list-group list-group-flush">
<li class="list-group-item">{{name}} from {{location.country}} is requesting {{loan_amount}} {{use}}</li>
</ul>
<div id="chartContainer{{id}}" class="img-thumbnail">
</div>
<script type="text/javascript">
var chart{{id}}= new CanvasJS.Chart("chartContainer{{id}}", {
title :{
text: "{{location.country}} corruption index"
},
height:100,
data: [{
type:"spline",
dataPoints: [
{ x: new Date(2017, 0), y: {{#if corruption.CPI_score_2017}}
{{corruption.CPI_score_2017}},
{{else}}
0,
{{/if}}
},
{ x: new Date(2016, 1), y: {{#if corruption.CPI_score_2016}}
{{corruption.CPI_score_2016}},
{{else}}
0,
{{/if}}
},
{ x: new Date(2015, 2), y: {{#if corruption.CPI_score_2015}}
{{corruption.CPI_score_2015}},
{{else}}
0,
{{/if}}
},
{ x: new Date(2014, 3), y: {{#if corruption.CPI_score_2014}}
{{corruption.CPI_score_2014}},
{{else}}
0,
{{/if}}
},
{ x: new Date(2013, 4), y: {{#if corruption.CPI_score_2013}}
{{corruption.CPI_score_2013}},
{{else}}
0,
{{/if}}
},
{ x: new Date(2012, 5), y: {{#if corruption.CPI_score_2012}}
{{corruption.CPI_score_2012}}
{{else}}
0
{{/if}}
}
]
}]
});
chart{{id}}.render();
console.log("#chartContainer{{id}}");
</script>
</ul>
</div>
</div>
{{/each}}
</div>

 

All of that will get you something that looks like this:

Leave a Reply

Powered by WordPress.com.

Up ↑

%d bloggers like this: