是否可以使用 sequelize 通过关联表中的属性过滤查询? [英] Is it possible to filter a query by the attributes in the association table with sequelize?

问题描述

我正在尝试通过连接表的属性过滤我的查询

I am trying to filter my query by the attributes of the joining table

我有 2 个表 Cities 和 Categories，我通过第三个表 CityCategory 将它们关联起来.这个想法是在 CityCategory.year 是特定整数时获取与城市关联的类别.

I have 2 tables Cities and Categories which I am associating through a third table CityCategory. The idea is to get the Categories associated with a City when CityCategory.year is a specific integer.

这是我指定关联的方式:

This is how I specified the associations:

module.exports = function(sequelize, DataTypes) {
    var CityCategory = sequelize.define('CityCategory', {
        year: {
            type: DataTypes.INTEGER,
            allowNull: false,
            validate: {
                notNull: true
            }
        }
    }, {
        indexes: [{
            unique: true,
            fields: ['CityId', 'CategoryId', 'year']
        }]
    });

    return CityCategory;
};

City.belongsToMany(models.Category, {
                    through: {
                        model: models.CityCategory
                    }
                });

Category.belongsToMany(models.City, {
                    through: {
                        model: models.CityCategory
                    }
                });

这是我目前未成功使用的查询:

This is the query I'm currently, unsuccessfully using:

City.find({
        where: {id: req.params.id},
        attributes: ['id', 'name'],
        include: [{
            model: Category,
            where: {year: 2015},
            attributes: ['id', 'name', 'year']
        }]
    })
    .then(function(city) {
        ...
    });

不幸的是，我不确定如何告诉 sequelize 使用 CityCategory 的年份属性，而不是在类别模型中搜索名为年份"的属性...

Unfortunately I'm not sure how to tell sequelize to use the CityCategory's year attribute instead of it searching for an attribute called 'year' in the Category model...

Unhandled rejection SequelizeDatabaseError: ER_BAD_FIELD_ERROR: Unknown column 'Category.CityCategory.year' in 'where clause'

这是可能的还是我必须手动编写自定义查询?

Is this possible or would I have to go and manually write my custom query?

非常感谢！

编辑

我一直在玩多一点，找到了解决方案！看起来有点乱，所以我相信一定有更好的方法.

I've been playing around a little more and found a solution! It seems a little messy so I'm sure there must be a better way.

City.find({
    where: {id: req.params.id},
    attributes: ['id', 'name'],
    include: [{
      model: Category,
      where: [
        '`Categories.CityCategory`.`year` = 2015'
      ],
      attributes: ['id', 'name', 'year']
    }]
  })
  .then(function(city) {
    ...
  });

推荐答案

查询直通表时，应使用through.where

include: [{
  model: Category,
  through: { where: {year: 2015}},
  attributes: ['id']
}]

您可能需要添加 required: true 以将包含转换为内连接

You might want to add required: true to turn the include to an inner join

这篇关于是否可以使用 sequelize 通过关联表中的属性过滤查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！