Sequelize Join on Non Primary Key

Question

I have two tables I need to associate. TableA has One TableB. I'm able to do this in the TableA Model:

TableA.hasOne( models.TableB, { as: 'TableB', foreignKey: 'someID' } );

Looking at the SQL, this tries to join TableA.ID and TableB.someID. What I actually want, is to join TableA.someNonPrimaryKey and TableB.someID.

How can I tell sequelize to join with someNonPrimaryKey?

Answer 1

I know this is old; I am responding for the sake of others that might need the answer.

You can now associate tables on non-primary key columns. In your case, the association would be:

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

Please note, the column someNonPrimaryKeyColumnOnTheSOurceModel must have unique set to true in the model definition.

You can read more about it here: https://sequelize.org/master/manual/assocs.html

Answer 2

For me it worked by doing both answers above from @thanh1101681 and @King James Enejo, only putting either sourcekey or targetkey didn't work!

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

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