My schema
db.user.hasMany(db.appointment, {
foreignKey: 'user_id'
})
db.user.hasMany(db.appointment, {
foreignKey: 'doctor_id'
})
db.appointment.belongsTo(db.user, {
foreignKey: 'user_id'
})
db.appointment.belongsTo(db.user, {
foreignKey: 'doctor_id'
})
The table user is common for doctor and user
After that I create 2 appointments
await Appointment.create({user_id: 13, doctor_id: 10 });
await Appointment.create({user_id: 14, doctor_id: 10 });
And try to query appointment of user has id 13
let user = await User.findByPk(13);
return res.json(user.getAppointments())
I expect only one appointment
{
"id": 1,
"createdAt": "2022-04-11T09:42:45.000Z",
"updatedAt": "2022-04-11T09:42:45.000Z",
"user_id": 13,
"doctor_id": 10
}
But ACTUALLY I got
[
{
"id": 1,
"createdAt": "2022-04-11T09:42:45.000Z",
"updatedAt": "2022-04-11T09:42:45.000Z",
"user_id": 13,
"doctor_id": 10
},
{
"id": 2,
"createdAt": "2022-04-11T09:42:55.000Z",
"updatedAt": "2022-04-11T09:42:55.000Z",
"user_id": 14,
"doctor_id": 10
}
]
As you can see the method user.getAppointments() return me 2 appointment for user 13, but actually he just has one appointment (another appointment is belong to user 14). I guest that I'm wrong or missing something here (something like mappedBy in Java Hibernate)
db.user.hasMany(db.appointment, {
foreignKey: 'user_id'
})
db.user.hasMany(db.appointment, {
foreignKey: 'doctor_id'
})
db.appointment.belongsTo(db.user, {
foreignKey: 'user_id'
})
db.appointment.belongsTo(db.user, {
foreignKey: 'doctor_id'
})
Could you please help to correct it? Thank in advance
If you define more than one association from the same model to the other same model then you should indicate unique aliases for associated models to distinguish them in all queries:
db.user.hasMany(db.appointment, {
foreignKey: 'user_id',
as: 'userAppointments'
})
db.user.hasMany(db.appointment, {
foreignKey: 'doctor_id',
as: 'doctorAppointments'
})
db.appointment.belongsTo(db.user, {
foreignKey: 'user_id',
as: 'user'
})
db.appointment.belongsTo(db.user, {
foreignKey: 'doctor_id',
as: 'doctor'
})
To get user appointments:
user.getUserAppointments()
To get doctor appointments:
doctor.getDoctorAppointments()
To get appointment's doctor:
appointment.getDoctor()
To get appointment's user:
appointment.getUser()