Sequelize:Include.where 按“父"模型属性过滤 [英] Sequelize: Include.where filtering by a 'parent' Model attribute

问题描述

我有两个相关的模型，目录和产品类别.后者有一个组合 PK，'id，language_id'.以下是简化的模型:

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

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

我试图从 ProductCategory 表中包含一些与 Catalog 相关的信息，但仅当 language_id 匹配时才包含.

I'm trying to include some info from ProductCategory table related to Catalog, but ONLY when the language_id matches.

目前我正在从两个表中获取所有可能的匹配项.这是现在的查询:

At the moment I'm getting all the possible matches from both tables. This is the query right now:

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

有没有办法使用 Catalog 中的属性来过滤两个模型具有相同语言的包含?

Is there a way to use an attribute from Catalog to filter the include where both models have the same language?

顺便说一句，我也尝试过更改 where 子句，没有任何后果:

By the way, I've also tried changing the where clause, without any consecuence:

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

推荐答案

Sequelize 为这种情况提供了一个额外的运算符 $col 所以你不必使用 sequelize.literal('...')(这更像是一个黑客).

Sequelize provides an extra operator $col for this case so you don't have to use sequelize.literal('...') (which is more a hack).

在您的示例中，用法如下所示:

In your example the usage would look like this:

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

这篇关于Sequelize:Include.where 按“父"模型属性过滤的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！