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

score 29 · Accepted Answer · answered Jul 28 '15 at 15:12

29

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

answered Jul 28 '15 at 15:12

Jan Aagaard Meier

28,078
8
95
66

What if there is no through model? – user3631341 Feb 24 '16 at 12:10
1

@user3631341 What do you mean - This question is about filtering on the association table (aka the through model) – Jan Aagaard Meier Feb 24 '16 at 13:57
1

Is it possible to get this working when using the instance version of `getWhatever`? For example, `hospital.getPatients({ include: [{ model: HospitalPatient, through: { where: { uniqueId: "test" } } }] })` where HospitalPatient is the `through` model? – Christian Droulers Jun 01 '16 at 21:37
1

The current answer includes the category fields also to the response. How can I remove that? – Roopak A Nelliat Jan 05 '19 at 07:05
`required` helps me alot when I don't know how to modify LEFT JOIN to INNER JOIN – zhangciwu Aug 31 '20 at 03:59

score 5 · Answer 2 · answered Sep 11 '16 at 13:13

5

For Sequelize v3 it appears the syntax is closer to what you suggested, that is:

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

answered Sep 11 '16 at 13:13

homebrand

1,143
1
9
9

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

2 Answers2

Linked