I’m new to mongoDB, I am trying to achieve the following SQL query on it. but could not find anything useful so far. can anyone tell equivalent mongoose query
select * from interviews inner join candidate on interviews.clientId = candidate._id inner join billing on appointment._id = billing.appointmentId where (' interviews.status= "upcoming", interviews.startTime= "2017-01-01", candidate.clientAgeGroup= "adult", candidate.candidatetatus= "new", billing.paymentStatus= "paid" ')
what I got so far is following
const [result, err] = await of(Interview.find({ ...filterQuery }).limit(perPage) .skip(perPage * page) .sort({ startTime: 'asc' }) .populate([{ path: 'candidateId', model: 'Candidate', select: 'firstName status avatar' }, { path: 'billingId', model: 'Billing', select: "status" }]));
UPDATE
I have following name and export scheme
//interview.model.js => mongodb show name as interview module.exports = mongoose.model('Interview', interviewSchema); //candidate.model.js => mongodb show name as candidate module.exports = mongoose.model('Candidate', candidateSchema);
Advertisement
Answer
You can use filter out objects included in resulting array using match
but in the case if it couldn’t find any, it would still return a null value. So in comparison this works similar to sql left join.
const [result, err] = await of(Interview.find({ ...filterQuery }).limit(perPage) .skip(perPage * page) .sort({ startTime: 'asc' }) .populate([{ path: 'candidateId', model: 'Candidate', select: 'firstName status avatar', match: {clientAgeGroup: "adult", candidatetatus: "new"} }, { path: 'billingId', model: 'Billing', select: "status", match: {paymentStatus: "paid"} }]));
Also see https://mongoosejs.com/docs/populate.html#query-conditions
If you need strictly a inner join then you can use mongodb aggregate pipeline.
Interview.aggregate([ { "$match": { status: "upcoming", startTime: "2017-01-01", } }, { '$lookup': { 'from': 'candidates', // this should be your collection name for candidates. 'localField': 'candidateId', // there should be an attribute named candidateId in interview model that refer to candidate collection 'foreignField': '_id', 'as': 'candidates' } }, { '$match': { 'candidates.clientAgeGroup': "adult", 'candidates.candidatetatus': "new" } }, { '$lookup': { 'from': 'billing', // this should be your collection name for billing. 'localField': 'billingId', // there should be an attribute named billingId in interview model that refer to billing collection 'foreignField': '_id', 'as': 'billing' } }, { '$match': { 'billing.paymentStatus': "paid" } }, { "$sort": { startTime: 1 } }, { "$limit": perPage }, { "$skip": perPage * page } ])