Is there a way to join tables that don't have associations defined using include in sequelize? This is not a duplicate of this. I am talking about tables that are not associated at all but having columns that I want to join on.
Example:
select * from bank
left outer join account
on account.bank_name = bank.name
The above query will return all records in table bank regardless of the existence of an account record where the specified constraints apply.
In sequelize this would look something like the following, if models bank and account were associated on account.bank_name = bank.name:
bank.findAll({
include: [{
model: account,
required: false,
}]
})
However, what if the models are not associated? Is there a way to write my own custom on section or equivalent:
bank.findAll({
include: [{
model: account,
required: false,
on: {
bank_name: Sequelize.col('bank.name')
}
}]
})
I vaguely remember reading something about this but I cannot seem to find that doc anywhere now. If you can point to the correct section in the docs it would be greatly appreciated.
To anyone who end up googling here: there is a workaround, not sure it was available back then, but as of 5.21.7 it works. you can pass association object to include parameter:
const res = await bank.findAll({
include: [
{
model: account,
association: new HasMany(bank, account, {/*options*/}),
},
],
})
HasMany is an association constructor, which can be taken from Sequelize.Associations, or sequelize instance. there are also other association constructors there. For TS you will need to cast manually since that property not in types.
UPD: for BelongsToMany you need to call _injectAttributes() on association prior usage, and I didnt check others apart from HasMany.