javascript - 如何使用 sequelize 或 sql 聚合来自不同表的总和？

Question

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

锻炼

电路

电路步骤

每个 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,
        allowNull: false
      },
      repeat_count: {
        type: DATATYPES.INTEGER,
        allowNull: false
      },
    },
  );

  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;
};

Answer 1

是的，聚合绝对是可能的。您可以使用 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'],
})

如果您需要进一步的帮助，请发表评论。这对我来说会很高兴，因为我自己已经坚持了很长时间。