mysql - 如何从 sequelize 中的多级关联中获取结果?
问题描述
我有 3 个模型国家、城市和办公室
它们相互关联。办公室属于城市,城市属于国家。现在我想做的是获得特定国家/地区内的办公室。我在下面尝试过,但它不起作用。
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;
};
解决方案
您可以像这样包含所有相关的嵌套模型
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;
推荐阅读
- swiftui - 如何制作一个接受任何对象的 swiftui viewmodifier?
- c# - 在 ASP.NET MVC 项目中使用来自其他项目的自定义 Redis 序列化程序
- jenkins - 停电后,Jenkins 不再管理连接到 SVN 存储库
- scala - 为具有构造函数的类型创建扩展方法,是否需要类型约束?
- javascript - TS 配置 JSX:ReactJSX 与 React
- constraint-programming - 检查 Docplex CP 解决方案中的可行性和未满足的约束
- aix - 即使已经安装了 bff,也要安装它
- reactjs - 如何处理错误 401(未经授权)React-Laravel
- pandas - 为什么我不能在数据框中选择内部级别的索引元素?
- html - 如何使用@media 查询覆盖“class=row”值