Sequelize multiple HasMany issue

Question

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

Answer 1

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