Sequelize: Include.where filtering using the 'parent' Model attribute

Question

I have two models related, Catalog and ProductCategory. The latter has a composite PK, 'id, language_id'. Here are simplified models:

var Catalog = sequelize.define("Catalog", { id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true }, user_id: { type: DataTypes.INTEGER, allowNull: false }, product_category_id: { type: DataTypes.STRING(7) }, language_id: { type: DataTypes.INTEGER }, ... more stuff ... } var ProductCategory = sequelize.define("ProductCategory", { id: { type: DataTypes.STRING(7), primaryKey: true }, language_id: { type: DataTypes.INTEGER, primaryKey: true }, ... more stuff ... } Catalog.belongsTo(models.ProductCategory, {foreignKey: 'product_category_id'}); 

I am trying to include some information from the ProductCategory table related to the directory, but ONLY when language_id matches.

At the moment, I get all possible matches from both tables. This is the request right now:

 Catalog.find({where: {id: itemId}, include: { model: models.ProductCategory, where: {language_id: /* Catalog.language_id */} } }) 

Is there a way to use an attribute from a directory to filter inclusions if both models use the same language?

By the way, I also tried changing the where expression without any consequences:

 where: {'ProductCategory.language_id': 'Catalog.language_id'} 

Answer 1

Sequelize provides an additional $col statement for this case, so you don't need to use sequelize.literal('...') (which is more dangerous).

In your example, usage would look like this:

 Catalog.find({where: {id: itemId}, include: { model: models.ProductCategory, where: { language_id: {$col: 'Catalog.language_id'} } } }) 

Answer 2

This seems to do the trick:

 where: {language_id: models.sequelize.literal('Catalog.language_id')}