时间戳

首页 > 解决方案 > 如何使用 sequelize 或 sql 从不同的表聚合总和？

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

问题描述

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

每个 Circuit 都有一个repeatCount，重复多少次，每个 CircuitStep 都有一个duration。

如何告诉模型使用andWorkout计算总和？这种聚合可能吗？durationCircuitSteps.durationCircuit.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;
};

标签： javascriptsqlnode.jssequelize.js

解决方案

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

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

推荐阅读