Sequelize join tables on non primary key

Question

My code:

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){
    // Authenticate
    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,


              //This is where the error is
              on:{
                  id: Sequelize.where(Sequelize.col("User.id"), "=",Sequelize.col("UserMail.from_user_id"))
              },


                attributes:['username']
            },
            // attributes:[], // TODO Set what columns are needed
            order:[['id', 'DESC']]
        }).then(mail=> {
            return res.status(200).send(mail);
        })

    })
}

When I use that I get "Unhandled rejection SequelizeDatabaseError: missing FROM-clause entry for table "User"" and I'm not sure what that means.

I've tried using

where:{
    id: UserMail.from_user_id;
}

But every time the query is executed it has "User"."id" = NULL so it never returns results. I've also tried all sorts of variations to get it to not be NULL but no variable I've tried works.

I just want to add a single column to the query, username from the Users table where from_user_id in the UserMail table is in the Users table. It sounds so simple, but I can't for the life of me seem to do it.

It's simple to join the table on primary keys by just using

include:{[User]}

and it will include where the User primary key is equal to the UserMail primary key, but that's not what I want. I want it on a UserMail column that's not the primary key.

Answer 1

Use targetKey instead of sourceKey

This is a code from my project, hope this helps

Country.hasMany(Region, { foreignKey: 'countrycode' })
Region.belongsTo(Country, { foreignKey: 'countrycode', targetKey:'countrycode' })

so, urs wold be

User.hasMany(UserMail, {foreignKey:'from_user_id'})
UserMail.belongsTo(User, {foreignKey: 'from_user_id', targetKey:'id'})

Answer 2

Gathering both "targetKey" and "sourceKey" is what worked for me, like this:

TableB.belongsTo(models.TableA, { foreignKey: 'someID', targetKey: 'notTableAID' } );

TableA.hasOne(TableB, {
  sourceKey: 'NonPrimaryKeyColumnOnTheSourceModel',
  foreignKey: 'matchingColumnOnTheTargetModel'
});