CODEWITHSUNDEEP
sails.jswaterlinesails-mongo
Jim Geurts
Jim Geurts

Reputation: 20419

How to filter a query based on collection from many-to-many

I have two model objects. Doctors and Hospitals. The model definitions look like:

module.exports = {

  schema: true,

  autoUpdatedAt: true,
  autoCreatedAt: true,
  attributes: {
    name: {
      type: 'string',
      required: true,
      unique: true
    },
    hospitals: {
      collection: 'hospital',
      via: 'doctors',
      dominant: true,
    },
  }
};

and 

module.exports = {
  schema: true,
  autoUpdatedAt: true,
  autoCreatedAt: true,
  attributes: {
    name: {
      type: 'string',
      required: true,
      unique: true
    },
    doctors: {
      collection: 'doctor',
      via: 'hospitals',
    },
  }
};

How can I query doctors that are mapped to certain hospitals? I read a couple posts about through keyword, but I wasn't able to get records to persist to the through/join table. Seems like if I could query the automatic join table, I could get it to work, but I'm curious if there is an "official" way to accomplish this type of query.

My current query looks like: Doctor.find().where({'hospitals': ['548303dcf49435ec4a01f2a2','548303cbf49435ec4a01f2a0']}).populate('hospitals').exec(function (err, doctors) { ... });

The underlying db is mongo, if that matters.

Upvotes: 4

Views: 1029

Answers (1)

Jim Geurts
Jim Geurts

Reputation: 20419

I did cheat a bit but things seem to be working. That said, I am interested if there's a better way to accomplish this type of query.

I created a model object that maps to the auto created join table. So in this case, my additional model object looks like:

module.exports = {

  schema: true,

  autoUpdatedAt: true,
  autoCreatedAt: true,
  tableName: 'doctor_hospitals__hospital_doctors',
  attributes: {

    doctor: {
      model: 'doctor',
      columnName: 'doctor_hospitals'
    },

    hospital: {
      model: 'hospital',
      columnName: 'hospital_doctors'
    }
  }
};

Now, I query the join table directly and use the results for a sub query:

DoctorHospital.find().where({'hospital': ['548303dcf49435ec4a01f2a2','548303cbf49435ec4a01f2a0']}).exec(function(err, doctorHospitals) {
  if(err) return next(err);

  Doctor.find().where({'id': _.pluck(doctorHospitals, 'doctor')}).populate('hospitals').exec(function (err, doctors){
    if(err) return next(err);

    return res.view({
      doctors: doctors
    });
  });
});

Upvotes: 1

Related Questions