Create View from multiple collections MongoDB

user_207746 0
Viewed 3770

I have following Mongo Schemas(truncated to hide project sensitive information) from a Healthcare project.

let PatientSchema = mongoose.Schema({_id:String})
let PrescriptionSchema = mongoose.Schema({_id:String, patient: { type: Number, ref: 'Patient', createdAt:Date }})
let ReportSchema = mongoose.Schema({_id:String, patient: { type: Number, ref: 'Patient', createdAt:Date }})
let EventsSchema = mongoose.Schema({_id:String, patient: { type: Number, ref: 'Patient', createdAt:Date }})

There is ui screen from the mobile and web app called Health history, where I need to paginate the entries from prescription, reports and events sorted based on createAt. So I am building a REST end point to get this heterogeneous data. How do I achieve this. Is it possible to create a "View" from multiple schema models so that I won't load the contents of all 3 schema to fetch one page of entries. The schema of my "View" should look like below so that I can run additional queries on it (e.g. find last report) 

{recordType:String,/* prescription/report/event */, createdDate:Date, data:Object/* content from any of the 3 tables*/}
mongoose aggregation-framework mongodb-query node.js mongodb
1 Answers

I can think of three ways to do this.

Imho the easiest way to achieve this is by using an aggregation something like this:

db.Patients.aggregate([
 {$match : {_id: <somePatientId>},
 {
   $lookup:
     {
       from: Prescription, // replicate this for Report and Event,
       localField: _id,
       foreignField: patient,
       as: prescriptions // or reports or events,
     }
  },
  { $unwind: prescriptions }, // or reports or events
  { $sort:{ $createDate : -1}},
  { $skip: <positive integer> },
  { $limit: <positive integer> },
])

You'll have to adapt it further, to also get the correct createdDate. For this, you might want to look at the $replaceRoot operator.

The second option is to create a new "meta"-collection, that holds your actual list of events, but only holds a reference to your patient as well as the actual event using a refPath to handle the three different event types. This solution is the most elegant, because it makes querying your data way easier, and probably also more performant. Still, it requires you to create and handle another collection, which is why I didn't want to recommend this as the main solution, since I don't know if you can create a new collection.

As a last option, you could create virtual populate fields in Patient, that automatically fetch all prescriptions, reports and events. This has the disadvantage that you can not really sort and paginate properly...

user_28562180
Related