CODEWITHSUNDEEP
javascriptsqlnode.jssequelize.js
Karl Taylor
Karl Taylor

Reputation: 5279

How to aggregate a total sum from a different table using sequelize or sql?

I have three models which are associated to each other:

Each Circuit has a repeatCount, how many times it is repeated, and each CircuitStep has a duration.

How can I tell the Workout model to calculate the sum of the duration using the CircuitSteps.duration and Circuit.repeatCount? Is this kind of aggregation possible?

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

Upvotes: 1

Views: 3783

Answers (1)

Hussain Nawaz Lalee
Hussain Nawaz Lalee

Reputation: 896

Yes the aggregation is definitely possibile. You can use a combination of sequelize.fn (SUM) and sequelize.col but since you need to access data from three levels it would be much better to use sequelize.literal and pass in a raw column select query. You can achieve this as follows:

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'],
})

Please leave a comment if you need further help. It would be a pleasure for me since I have been stuck on this for a long time myself.

Upvotes: 4

Related Questions