Here is the code snippet:
User.hasMany(UserMail, {foreignKey:'to_user_id', sourceKey:'id'});
User.hasMany(UserMail, {foreignKey:'from_user_id', sourceKey:'id'});
UserMail.belongsTo(User, {foreignKey: 'from_user_id'})
UserMail.belongsTo(User, {foreignKey: 'to_user_id'})
function getUserMail(req,res){
// Authentication
jwt.verify(req.headers.jwt, process.env.JWT_KEY,function(err,decoded) {
if (err) {
return res.status(401).send("Invalid token");
}
let id = decoded.id;
return UserMail.findAll({
where: {
to_user_id: id,
to_user_deleted: false
},
include:{
model: User,
on:{
id: Sequelize.where(Sequelize.col("User.id"), "=",Sequelize.col("UserMail.from_user_id"))
},
attributes:['username']
},
order:[['id', 'DESC']]
}).then(mail=> {
return res.status(200).send(mail);
})
})
}
Upon using this code, I encountered an error "Unhandled rejection SequelizeDatabaseError: missing FROM-clause entry for table "User"" but couldn't figure out what it means.
I attempted using
where:{
id: UserMail.from_user_id;
}
However, every execution of this query resulted in "User"."id" = NULL, failing to return any results. Various attempts were made to prevent NULL values but none succeeded.
The goal is to add a single column, username from the Users table, based on the relationship between from_user_id in the UserMail table and the Users table. Despite sounding simple, achieving this seems challenging.
While joining tables based on primary keys is easy by using
include:{[User]}
This method links User primary key to UserMail primary key, whereas the requirement is to join them based on a non-primary key column in UserMail.