前端之家

  1. 首页
  2. 问答

如何使用sequelize或sql汇总来自其他表的总和？

• 问答

我有三个相互关联的模型：

  • 锻炼
    • 电路
      • CircuitStep

每个Circuit都有一个repeatCount，重复了几次，每个CircuitStep都有一个duration。

如何告诉Workout模型使用duration和CircuitSteps.duration计算Circuit.repeatCount的总和？这种聚合有可能吗？

// models/Workout.js
module.exports = (sequelize,DATATYPES) => {
  const Workout = sequelize.define(
    "workout",{
      name: { type: DATATYPES.STRING },duration: { type: DATATYPES.STRING },}
  );

  Workout.associate = function(models) {
    models.Workout.hasMany(models.Circuit);
  };

  return Workout;
};

// models/Circuit.js
module.exports = (sequelize,DATATYPES) => {
  const Circuit = sequelize.define(
    "circuit",{
      name: {
        type: DATATYPES.STRING,allowNull: false
      },workout_id: {
        type: DATATYPES.INTEGER,repeat_count: {
        type: DATATYPES.INTEGER,},);

  Circuit.associate = function(models) {
    models.Circuit.hasMany(models.CircuitStep);
    models.Circuit.belongsTo(models.Workout);
  };

  return Circuit;
};

module.exports = (sequelize,DATATYPES) => {
  const CircuitStep = sequelize.define(
    "circuit_step",{
      duration: {
        type: DATATYPES.INTEGER
      },exercise_id: {
        type: DATATYPES.INTEGER
      },circuit_id: {
        type: DATATYPES.INTEGER
      },}
  );

  CircuitStep.associate = function(models) {
    models.CircuitStep.belongsTo(models.Circuit);
  };

  return CircuitStep;
};
m258494824 回答：如何使用sequelize或sql汇总来自其他表的总和？

是的，聚集肯定是可能的。您可以结合使用sequelize.fn（SUM）和sequelize.col，但是由于您需要从三个级别访问数据，因此最好使用sequelize.literal并传递原始列选择查询。您可以按照以下步骤实现：

const workout = await Workout.findAll({
    // The query will refer to Workout model as `workout`
    attributes: [

        // Notice how I referred to the attributes using the `tableNames` in backticks becuase `->` is
        // an invalid character in an SQL query but sequelize when generating the SQL query assigns
        // this alias to the nested included tables
        [[sequelize.literal('SUM(`circuits`.duration * `circuit->circuitSteps`.repeatCount'),'total']]
    ],include: [
        {
            // This will join Workout to Circuit table
            // The query generated by sequelize will refer to Circuit as `circuits`
            // since Workout.hasMany(Circuit)
            model: Circuit,attributes: [],// This will join Circuit to CircuitStep
            // The query generated will refer to CircuitStep as `circuit->circuitSteps` 
            // If not working then log the query and the alias in the SQL query for the table
            include: [
                {
                    model: CircuitStep,// You can fetch any attributes you want I prefer aggregating everything on
                    // top level using sequelize.literal
                    attributes: []
                }
            ]
        }
    ],group: ['workout.id'],})

如果您需要进一步的帮助，请发表评论。对我来说，这是我的荣幸，因为我本人已经坚持了很长时间。

javascriptnode.jssequelize.jssql
本文链接：https://www.f2er.com/3167624.html

大家都在问