使用 Sequelize 计算关联条目

Question

我有两张表， locations 和 sensors 。 sensors 中的每个条目都有一个指向 locations 的外键。使用 Sequelize，如何从 — 中获取所有条目以及 locations 中与 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;
};

原文由 MikkoP 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

使用 findAll() 与 include() 和 sequelize.fn() 用于 —f28618ded18306b0cac320df3d1ab04e42— 和 COUNT

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

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

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

原文由 alecxe 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

用于计算与 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
})

笔记 ：

不知何故，这个查询会产生一个错误，比如 sensors.location is not exists in field list。 这是因为由上述 sequelize 查询形成的子查询。

所以解决这个问题的方法是提供 subQuery: false 这样的例子

Location.findAll({
        subQuery: false,
        attributes: {
            include: [[Sequelize.fn('COUNT', Sequelize.col('sensors.location')), 'sensorCounts']]
        },
        include: [{
            model: Sensor, attributes: []
        }],
        group: ['sensors.location']
    })

注意： **有时这也可能会生成 mysql 配置的错误 bcz，默认情况下在 sqlMode 中包含 only-full-group-by，需要将其删除才能正常工作。

错误看起来像这样..**

错误：SELECT 列表的表达式 #1 不在 GROUP BY 子句中并且包含非聚合列“db.table.id”，它在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容

因此，要解决此错误，请遵循此答案

SELECT 列表不在 GROUP BY 子句中并且包含非聚合列….与 sql_mode=only_full_group_by 不兼容

现在这将成功生成所有关联的计数

希望这会对您或其他人有所帮助！

原文由 Aman Kumar Gupta 发布，翻译遵循 CC BY-SA 4.0 许可协议