是否可以通过 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 的 year 属性，而不是在 Category 模型中搜索名为year"的属性...

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表时，应该使用through.where

When querying the through table, you should use 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屋！