I have a transaction model that hasOne stripePayment. I want to be able to retrieve a transaction with it's associated stripePayment.
When I run the following query:
const data = await models.Transaction.findOne({
where: { clientId },
include: [
{
model: models.StripePayment,
}
]
});
It is trying to left outer join where Transaction`.`id` = `StripePayment`.`stripePaymentId when it should be the other way around. i.e. Transaction`.`stripePaymentId` = `StripePayment`.`id
My tables look something like this
transactions
=======
id | stripePaymentId
---------------------
1 | 1a
2 | 2b
3 | 3c
4 | 4d
stripePayments
=======
id | amount
---------------------
1a | 100
2b | 101
3c | 102
4d | 103
I then have my models with the associations defined like this:
class Transaction extends Model {
static associate(models) {
this.hasOne(models.StripePayment, {
foreignKey: 'id'
});
}
}
Transaction.init(
{
id: {
allowNull: false,
primaryKey: true,
type: DataTypes.UUID,
defaultValue: DataTypes.UUIDV4,
},
stripePaymentId: {
type: DataTypes.UUID,
allowNull: true,
foreignKey: true,
references: {
model: stripePayment,
key: 'id',
},
}
},
{
sequelize,
modelName: 'Transaction',
}
);
and
class StripePayment extends Model {
static associate(models) {
this.belongsTo(models.Transaction, {
foreignKey: 'stripePaymentId'
});
}
}
StripePayment.init(
{
id: {
allowNull: false,
primaryKey: true,
type: DataTypes.UUID,
defaultValue: DataTypes.UUIDV4,
},
amount: {
type: DataTypes.INTEGER,
allowNull: false,
}
},
{
sequelize,
modelName: 'StripePayment',
}
);
I was under the impression that a one to one relationship should have a foreign key on the origin table.
How can I tell sequelize to join on transaction.stripePaymentId === stripePayment.id?
You should use belongsTo from a model that has foreign key field and hasOne or hasMany for a parent model to which another model has a reference (foreign key).
Also, you should indicate foreignKey option while defining associations if a foreign key column is named other than <Child table mode>+<id>.
class Transaction extends Model {
static associate(models) {
this.belongsTo(models.StripePayment, {
foreignKey: 'stripePaymentId'
});
}
}
...
class StripePayment extends Model {
static associate(models) {
this.hasOne(models.Transaction, {
foreignKey: 'stripePaymentId'
});
}
}
Please pay attention that foreignKey option values should be the same for pairs belongsTo/hasOne(hasMany).