Checking if two values match from one to another table

Viewed 36

So I started doing a app with JavaScript ORM- Sequelize. I have a table called Workouts.js where the exercises are the id's in a table called Exercises.js.

Workouts.js

id exercise_one exercise_two exercise_three date
1 1 2 4 2022-02-21
2 4 3 2 2022-02-23
3 3 1 1 2022-02-25

I have another table called Workout_Volume.js which has the following values:

Workout_Volume.js

id workout_id exercise_id sets reps weight
1 1 1 3 10 60
2 1 4 4 12 40
3 3 3 3 15 30
4 2 4 5 5 80

So my question is what is the correct way to validate in Workout_Volume.js workouit_id and exercise_id match as in Workouts.js when creating and updating Workout_Volume.js?

Workouts.js

const workout = (sequelize, DataTypes) => {
    const Workout = sequelize.define(
        'workouts', {
        exercise_one: {
            type: DataTypes.INTEGER(), allowNull: false,
            references: { model: "exercises", key: "id" },
            validate: {
                notNull: { msg: "You need to provide exercise_id !" }
            }
        },
        exercise_two: {
            type: DataTypes.INTEGER(), allowNull: true,
            references: { model: "exercises", key: "id" },
        },
        exercise_three: {
            type: DataTypes.INTEGER(), allowNull: true,
            references: { model: "exercises", key: "id" },
        },
        exercise_four: {
            type: DataTypes.INTEGER(), allowNull: true,
            references: { model: "exercises", key: "id" },
        },
        exercise_five: {
            type: DataTypes.INTEGER(), allowNull: true,
            references: { model: "exercises", key: "id" },
        },
        exercise_six: {
            type: DataTypes.INTEGER(), allowNull: true,
            references: { model: "exercises", key: "id" },
        },
        exercise_seven: {
            type: DataTypes.INTEGER(), allowNull: true,
            references: { model: "exercises", key: "id" },
        },
        exercise_eight: {
            type: DataTypes.INTEGER(), allowNull: true,
            references: { model: "exercises", key: "id" },
        },
        date: {
            type: DataTypes.DATEONLY, allowNull: false,
            validate: {
                notNull: { msg: "You need to provide date !" }
            }
        }
    },
        {
            timestamps: false,
            freezeTableName: true,
        })
        Workout.associate = models => {
            Workout.belongsToMany(models.exercises, {
                foreignKey: "exercise_one",
                through: 'exerciseOne',
                targetKey: "id"
            })
            Workout.belongsToMany(models.exercises, {
                foreignKey: "exercise_two",
                through: 'exercisTwo',
                targetKey: "id"
            })
            Workout.belongsToMany(models.exercises, {
                foreignKey: "exercise_three",
                through: 'exerciseThree',
                targetKey: "id"
            })
            Workout.belongsToMany(models.exercises, {
                foreignKey: "exercise_four",
                through: 'exerciseFour',
                targetKey: "id"
            })
            Workout.belongsToMany(models.exercises, {
                foreignKey: "exercise_five",
                through: 'exerciseFive',
                targetKey: "id"
            })
            Workout.belongsToMany(models.exercises, {
                foreignKey: "exercise_six",
                through: 'exerciseSix',
                targetKey: "id"
            })
            Workout.belongsToMany(models.exercises, {
                foreignKey: "exercise_seven",
                through: 'exerciseSeven',
                targetKey: "id"
            })
            Workout.belongsToMany(models.exercises, {
                foreignKey: "exercise_eight",
                through: 'exerciseEight',
                targetKey: "id"
            })
            Workout.belongsTo(models.workout_volume, {
                foreignKey:"id",
                targetKey: "workout_id",
                through: "workout_volume"
            })
        }

    Workout.sync()
    return Workout
}
module.exports = workout

Workout_Volume.js

const workoutVolume = (sequelize, DataTypes) => {
    const Workout_Volume = sequelize.define(
        'workout_volume', {
        workout_id: {
            type: DataTypes.INTEGER, allowNull: false,
            references: { model: "workouts", key: "id" },
            validate: {
                notNull: { msg: "You need to provide workout_id !" }
            }
        },
        exercise_id: {
            type: DataTypes.INTEGER, allowNull: false,
            validate: {
                notNull: { msg: "You need to provide exercise_id !" }
            },
        },
        sets: {
            type: DataTypes.INTEGER, allowNull: false,
            validate: {
                min: 1,
                max: 100,
                notNull: { msg: "You need to provide sets!" }
            }
        },
        reps: {
            type: DataTypes.INTEGER, allowNull: false,
            validate: {
                min: 1,
                max: 100,
                notNull: { msg: "You need to provide reps!" }
            }
        },
        weight: {
            type: DataTypes.INTEGER, allowNull: false,
            validate: {
                min: 0,
                max: 1000,
                notNull: { msg: "You need to provide sets!" }
            }
        }
    },
        {
            timestamps: false,
            freezeTableName: true,
        },
    )
    Workout_Volume.associate = models => {
        Workout_Volume.belongsTo(models.workouts,{
           foreignKey: "workout_Id",
           as: "workoutId",
           targetKey: "id" 
        })
    }
    Workout_Volume.sync()
    return Workout_Volume
}

module.exports = workoutVolume

My thoughts on making is:

  • To make associations with exercise_id to each exercise value in Workouts.js. The problem is that the exercises are 8 and I think the project will become more hard-coded;
  • Don't make assosiactions but make a for loop where I will check if the requested workout_id and exercises_id match the Workout.js values; Or if there is better way to consturct the tables so it makes more sence
  • Or if there is better way to construct tables or something else.

workoutVolume.controller.js

const models = require('../models');
const Workout_Volume = models.workout_volume;

exports.getAllWorkouts_Volume = async (req, res) => {
    try {
        const workout_volume = await Workout_Volume.findAll()
        return res.status(200).send(workout_volume)
    } catch (error) {
        return res.status(500).send(error)
    }
}

exports.getWorkout_Volume = async (req, res) => {
    try {
        const workout_volume = await Workout_Volume.findByPk(req.params.id)
        res.status(200).send(workout_volume)
    } catch (error) {
        return res.status(500).send(error)
    }
}

exports.createWorkout_Volume = async (req, res) => {
    try {
        const exerciseExists = await Workout_Volume.findAll({
            where: {
                exercise_id: req.body.exercise_id,
                workout_id: req.body.workout_id
            },
            attributes: ["exercise_id", "workout_id"]
        })
        if (exerciseExists.length !== 0) {
            return res.status(500).send("Exercise already done for this workout!")
        }
        await Workout_Volume.create(req.body)
        return res.status(201).send(req.body)
    } catch (error) {
        return res.status(500).send(error)
    }
}

exports.updateWorkout_volume = async (req, res) => {
    try {
        const workout_volume = await Workout_Volume.findByPk(req.params.id)
        console.log(req.body)
        const exerciseExists = await Workout_Volume.findAll({
            where: {
                exercise_id: req.body.exercise_id,
                workout_id: req.body.workout_id
            },
            attributes: ["exercise_id", "workout_id"]
        })
        if (exerciseExists.length !== 0) {
            return res.status(500).send("Exercise already done for this workout!")
        }
        await workout_volume.update(req.body)
        return res.status(200).send(req.body)
    } catch (error) {
        return res.status(500).send(error)
    }
}
The problem here is that when I create I can't create invalid workout_id but can create exercise_id which is not in Workout.js. Also when I try to update it already the length is !== 0

1 Answers

You definitely need to create a table/model Workout_Excercise instead of adding columns in Workout table/model. That way you will have as many exercises in a certain workout as you want.
And the second great benefit of having Workout_Excercise is that you will be able to create a correct foreign key from Workout_Volume to Workout_Excercise:

Workout.hasMany(models.workoutExercises, {
                foreignKey: "workoutId",
            })
WorkoutExercise.belongsTo(models.exercises, {
  foreignKey: "exerciseId",
})
WorkoutExercise.hasMany(models.workout_volume, {
                foreignKey: "workoutExerciseId",
            })
WorkoutVolume.belongsTo(models.workout_exercise, {
                foreignKey: "workoutExerciseId",
            })
Related