如何在sequelize中从多级关联中获取结果？
如何在sequelize中从多级关联中获取结果？
提问于 2018-08-22 18:55:14
我有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;
};回答 2
Stack Overflow用户
回答已采纳
发布于 2018-08-22 19:26:41
您可以包含所有相关的嵌套模型，如下所示
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;Stack Overflow用户
发布于 2018-08-22 19:13:00
您可以直接从include中查询国家/地区，并使用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
}]
}
]
});页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51965298
复制相关文章
相似问题
腾讯云开发者
