How to filter a many-to-many collection based query
I have two model objects. Doctors and hospitals. The model definitions look like this:
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 request doctors that appear in some hospitals? I read a couple of posts about the keyword through
, but I was unable to get the records to be stored in the end-to-end / join table. It looks like if I could query the auto 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 request looks like this: Doctor.find().where({'hospitals': ['548303dcf49435ec4a01f2a2','548303cbf49435ec4a01f2a0']}).populate('hospitals').exec(function (err, doctors) { ... });
The db is based on mongo, if that matters.
source to share
I cheated a little, but everything works. However, I am wondering if there is a better way to accomplish this type of request.
I created a model object that maps to an auto-generated connection table. So in this case my additional model object looks like this:
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 directly query the join table and use the results for the subquery:
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
});
});
});
source to share