Sequelize is a great ORM for Express which supports not just MySQL but also PostgreSQL and SQLite. Just like any other ORM, to perform JOIN queries using models you need to first define an association between the two tables (models). This association can be hasOne, hasMany, belongsTo and so on. Now this becomes tricky if we have to define a relation between two tables which are not related directly by any foreign key.
So let’s consider the case where we have two tables, customer_purchased_products and customer_reviews. As the name says the former is used for saving products purchased by each customer and the latter one for reviews posted by a customer for a purchased product:
customer_purchased_products
| cpp_id | customer_id | product_id | amount |
|---|
customer_reviews
| cr_id | customer_id | product_id | review |
|---|
Let’s say we have two classes CustomerPurchasedProduct and CustomerReview which are the Sequelize models for these tables. Even if there is no explicit foreign key relation defined between them still we can define a relation using
CustomerPurchasedProduct.belongsTo(
CustomerReview,
{
targetKey: 'customer_id',
foreignKey: 'customer_id',
scope: {
product_id: { $col: 'CustomerPurchasedProduct.product_id' }
}
}
)
time-frame