Join without foreign key constraint in Sequelize

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' }
        }
    }
)

Join the discussion

Leave a Reply

Your email address will not be published. Required fields are marked *