pire pire - 2 months ago 31
Node.js Question

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

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

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