node.jssequelize.jsone-to-manyhas-manybelongs-to

Sequelize multiple HasMany issue


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


Solution

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