使用 Sequelize 计算关联条目 [英] Counting associated entries with Sequelize

问题描述

我有两个表，locations 和 sensors.sensors 中的每个条目都有一个指向 locations 的外键.使用 Sequelize，我如何从 locations 中获取所有条目以及 sensors 中与 locations 中的每个条目相关联的条目总数?p>

原始 SQL:

选择`位置`.*，COUNT(`sensors`.`id`) AS`sensorCount`从`位置`加入`sensors` ON`sensors`.`location`=`locations`.`id`;GROUP BY `locations`.`id`;

型号:

module.exports = function(sequelize, DataTypes) {var Location = sequelize.define("位置", {ID: {类型:DataTypes.INTEGER.UNSIGNED，主键:真},名称:DataTypes.STRING(255)}, {类方法:{关联:功能(模型){Location.hasMany(models.Sensor, {外键:位置"});}}});返回位置；};module.exports = function(sequelize, DataTypes) {var Sensor = sequelize.define("Sensor", {ID: {类型:DataTypes.INTEGER.UNSIGNED，主键:真},名称:DataTypes.STRING(255)，类型: {类型:DataTypes.INTEGER.UNSIGNED，参考: {型号:传感器类型"，关键:身份证"}},地点: {类型:DataTypes.INTEGER.UNSIGNED，参考: {模型:位置"，关键:身份证"}}}, {类方法:{关联:功能(模型){Sensor.belongsTo(models.Location, {外键:位置"});Sensor.belongsTo(models.SensorType, {外键:类型"});}}});返回传感器；};

解决方案

使用 findAll() 与 include() 和 sequelize.fn()COUNT 的代码>:

Location.findAll({属性: {包括:[[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]]},包括: [{型号:传感器，属性:[]}]});

或者，您可能还需要添加 group:

Location.findAll({属性: {包括:[[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]]},包括: [{型号:传感器，属性:[]}],组:['Location.id']})

I have two tables, locations and sensors. Each entry in sensors has a foreign key pointing to locations. Using Sequelize, how do I get all entries from locations and total count of entries in sensors that are associated with each entry in 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;
};

解决方案

Use findAll() with include() and sequelize.fn() for the COUNT:

Location.findAll({
    attributes: { 
        include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]] 
    },
    include: [{
        model: Sensor, attributes: []
    }]
});

Or, you may need to add a group as well:

Location.findAll({
    attributes: { 
        include: [[Sequelize.fn("COUNT", Sequelize.col("sensors.id")), "sensorCount"]] 
    },
    include: [{
        model: Sensor, attributes: []
    }],
    group: ['Location.id']
})

这篇关于使用 Sequelize 计算关联条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！