如何从sequelize中的多个级别关联中获得结果？

cuxqih21 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(382)

我有三个模型国家，城市和办公室
它们是相互联系的。办公室属于城市，城市属于国家。现在我要做的是在特定国家设立办事处。我在下面试过，但不起作用。

Office.findAll({
  where: {'$Country.id$': 1},
  include: [
    { 
      model: City,
      as: 'city',
      include: [{model: Country, as: '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;
}

城市

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;
}

办公室

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;
};

mysql sequelize.js

来源：https://stackoverflow.com/questions/51965298/how-to-get-results-from-multiple-level-associations-in-sequelize

2条答案

按热度按时间

2hh7jdfx1#

可以像这样包含所有相关的嵌套模型

const where = {
    'city.country.id': 1
};
Office.findAll({ where, include: [{ all: true, nested: true }]});

它会导致

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;

展开查看全部

赞(0）回复(0）举报 2021-06-20

p4rjhz4m2#

您可以从include直接从country查询，并使用 required : true ，请尝试以下操作：

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
            }]
    }
  ]
});

展开查看全部

赞(0）回复(0）举报 2021-06-20

我来回答

如何从sequelize中的多个级别关联中获得结果？

2条答案

相关问题

热门标签

最新问答