How to get results from multiple level associations in sequelize?

Question

I have 3 models Country, City and Office

They are associated with each other. Office Belongs To City, City Belongs to Country. Now what I want to do is get Office that are within specific Country. I have tried below but it does not work.

Office.findAll({
  where: {'$Country.id$': 1},
  include: [
    { 
      model: City,
      as: 'city',
      include: [{model: Country, as: 'country'}]
    }
  ]
});

Country

module.exports = (sequelize, DataTypes) => {
    let Country = sequelize.define('Country', {
        id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
        code: {type: DataTypes.STRING, allowNull: false, unique: true },
        name: DataTypes.STRING
    });
    Country.associate = (models) => {
        Country.hasMany(models.City, {as: 'cities'});
    };
    return Country;
}

City

module.exports = (sequelize, DataTypes) => {
    let City = sequelize.define('City', {
        id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
        name: {type: DataTypes.STRING, allowNull: false, unique: true},
    });

    City.associate = (models) => {
        City.belongsTo(models.Country, {as: 'country'});
        City.hasMany(models.Office, {as: 'offices'});
    };

    return City;
}

Office

module.exports = (sequelize, DataTypes) => {
  let Office= sequelize.define('Office', {
    id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
    name: DataTypes.STRING,
    details: DataTypes.TEXT,
    latitude: DataTypes.DECIMAL(10, 8),
    longitude: DataTypes.DECIMAL(11, 8),
  });

  Office.associate = (models) => {
    Office.belongsTo(models.City, {as: 'city'});
  };

  return Office;
};

Answer 1

You can include all related nested models like this

const where = {
    'city.country.id': 1
};

Office.findAll({ where, include: [{ all: true, nested: true }]});

it will result in

SELECT
    `Office`.`id`,
    `Office`.`name`,
    `Office`.`details`,
    `Office`.`latitude`,
    `Office`.`longitude`,
    `Office`.`createdAt`,
    `Office`.`updatedAt`,
    `Office`.`cityId`,
    `Office`.`CityId`,
    `city`.`id` AS `city.id`,
    `city`.`name` AS `city.name`,
    `city`.`createdAt` AS `city.createdAt`,
    `city`.`updatedAt` AS `city.updatedAt`,
    `city`.`CountryId` AS `city.CountryId`,
    `city`.`countryId` AS `city.countryId`,
    `city->country`.`id` AS `city.country.id`,
    `city->country`.`code` AS `city.country.code`,
    `city->country`.`name` AS `city.country.name`,
    `city->country`.`createdAt` AS `city.country.createdAt`,
    `city->country`.`updatedAt` AS `city.country.updatedAt`
FROM
    `Offices` AS `Office`
    LEFT OUTER JOIN `Cities` AS `city` ON `Office`.`cityId` = `city`.`id`
    LEFT OUTER JOIN `Countries` AS `city->country` ON `city`.`countryId` = `city->country`.`id`
WHERE
    `Office`.`city.country.id` = 1;

Answer 2

You can query from Country directly from include , and use required : true , try this :

Office.findAll({
  include: [
    { 
      model: City,
      as: 'city',
      required : true , <----- Make sure will create inner join
      include: [
            {
                model: Country, 
                as: 'country' ,
                required : true , // <----- Make sure will create inner join
                where : { 'id' : 1 } // <-------- Here
            }]
    }
  ]
});