我有两张桌子,locations
and sensors
。每个条目在sensors
有一个外键指向locations
。使用 Sequelize,如何获取所有条目locations
以及条目总数sensors
与每个条目相关联locations
?
Raw SQL:
SELECT
`locations`.*,
COUNT(`sensors`.`id`) AS `sensorCount`
FROM `locations`
JOIN `sensors` ON `sensors`.`location`=`locations`.`id`;
GROUP BY `locations`.`id`;
Models:
module.exports = function(sequelize, DataTypes) {
var Location = sequelize.define("Location", {
id: {
type: DataTypes.INTEGER.UNSIGNED,
primaryKey: true
},
name: DataTypes.STRING(255)
}, {
classMethods: {
associate: function(models) {
Location.hasMany(models.Sensor, {
foreignKey: "location"
});
}
}
});
return Location;
};
module.exports = function(sequelize, DataTypes) {
var Sensor = sequelize.define("Sensor", {
id: {
type: DataTypes.INTEGER.UNSIGNED,
primaryKey: true
},
name: DataTypes.STRING(255),
type: {
type: DataTypes.INTEGER.UNSIGNED,
references: {
model: "sensor_types",
key: "id"
}
},
location: {
type: DataTypes.INTEGER.UNSIGNED,
references: {
model: "locations",
key: "id"
}
}
}, {
classMethods: {
associate: function(models) {
Sensor.belongsTo(models.Location, {
foreignKey: "location"
});
Sensor.belongsTo(models.SensorType, {
foreignKey: "type"
});
}
}
});
return Sensor;
};