MySQL 使用 Sequelize 计算相关条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37817808/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Counting associated entries with Sequelize
提问by MikkoP
I have two tables, locationsand sensors. Each entry in sensorshas a foreign key pointing to locations. Using Sequelize, how do I get all entries from locationsand total count of entries in sensorsthat are associated with each entry in locations?
我有两张桌子，locations和sensors。中的每个条目sensors都有一个指向 的外键locations。使用 Sequelize，我如何从中的每个条目获取所有条目locations和条目总数？sensorslocations
Raw SQL:
原始 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;
};
回答by alecxe
Use findAll()with include()and sequelize.fn()for the COUNT:
使用findAll()与include()和sequelize.fn()为COUNT：
Location.findAll({
attributes: {
include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]]
},
include: [{
model: Sensor, attributes: []
}]
});
Or, you may need to add a groupas well:
或者，您可能还需要添加一个group：
Location.findAll({
attributes: {
include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]]
},
include: [{
model: Sensor, attributes: []
}],
group: ['Location.id']
})
回答by Arif Fathurrohman
Location.findAll({
attributes: {
include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]]
},
include: [{
model: Sensor, attributes: []
}]
});
and it works. but when i add "limit", i got error: sensors undefined
它有效。但是当我添加“限制”时，出现错误：传感器未定义
回答by Aman Kumar Gupta
For Counting associated entries with Sequelize
使用 Sequelize 计算关联条目
Location.findAll({
attributes: {
include: [[Sequelize.fn('COUNT', Sequelize.col('sensors.location')), 'sensorCounts']]
}, // Sequelize.col() should contain a attribute which is referenced with parent table and whose rows needs to be counted
include: [{
model: Sensor, attributes: []
}],
group: ['sensors.location'] // groupBy is necessary else it will generate only 1 record with all rows count
})
Note :
笔记 ：
Some how, this query generates a error like sensors.location is not exists in field list.This occur because of subQuery which is formed by above sequelize query.
不知怎么的，这个查询会产生一个错误，比如sensors.location is not exists in field list。这是因为 subQuery 是由上述 sequelize 查询形成的。
So solution for this is to provide subQuery: false like example
所以对此的解决方案是提供 subQuery: false like example
Location.findAll({
subQuery: false,
attributes: {
include: [[Sequelize.fn('COUNT', Sequelize.col('sensors.location')), 'sensorCounts']]
},
include: [{
model: Sensor, attributes: []
}],
group: ['sensors.location']
})
Note:**Sometime this could also generate a error bcz of mysql configuration which by default contains only-full-group-by in sqlMode, which needs to be removed for proper working.
注意：**有时这也会产生 mysql 配置的错误 bcz，默认情况下，sqlMode 中只包含-full-group-by，需要将其删除才能正常工作。
The error will look like this..**
错误看起来像这样..**
Error : Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.table.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
错误：SELECT 列表的表达式 #1 不在 GROUP BY 子句中，并且包含非聚合列“db.table.id”，该列在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容
So to resolve this error follow this answer
因此，要解决此错误，请遵循此答案
SELECT 列表不在 GROUP BY 子句中并且包含非聚合列......与 sql_mode=only_full_group_by 不兼容
Now this will successfully generate all associated counts
现在这将成功生成所有关联的计数
Hope this will help you or somebody else!
希望这会帮助你或其他人！
