我有两个表,locations和sensors。中的每个条目sensors都有一个指向的外键locations。使用Sequelize,如何获取与中的每个条目相关联的所有条目locations以及其中的条目总数?sensors``locations
locations
sensors
sensors``locations
原始SQL:
SELECT `locations`.*, COUNT(`sensors`.`id`) AS `sensorCount` FROM `locations` JOIN `sensors` ON `sensors`.`location`=`locations`.`id`; GROUP BY `locations`.`id`;
楷模:
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; };
使用findAll()与include()和sequelize.fn()为COUNT:
findAll()
include()
sequelize.fn()
COUNT
Location.findAll({ attributes: { include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]] }, include: [{ model: Sensor, attributes: [] }] });
或者,您可能还需要添加一个group:
group
Location.findAll({ attributes: { include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]] }, include: [{ model: Sensor, attributes: [] }], group: ['Location.id'] })