CODEWITHSUNDEEP
javascriptnode.jsmongodbmongoose
Mani
Mani

Reputation: 2563

Conditional joins on collections using mongoose

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);

Upvotes: 1

Views: 1294

Answers (1)

v1shva
v1shva

Reputation: 1599

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 }
])

Upvotes: 3

Related Questions