Is it possible to filter a query by the attributes in the association table with sequelize?

Question

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

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) {         ...     }); 

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?

Many thanks in advance!

edit

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) {     ...   }); 

Answer 1

When querying the through table, you should use through.where

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

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