I have 4 tables: Users, Workouts, Exercises, and Results. A "User" posts "Results" for "Exercises" which are linked to a single "Workout". But when the user posts results, since there are multiple exercises, results for one workout can be linked with a unique "post_id". I would like to know how many total minutes a user exercised based on how many "post_ids" they provided which can be linked to the "Workouts" table where a "workout_duration" column shows how many minutes each workout lasts. Here is some sample data, where in this case the workout (workout_id=1) has two exercises and has a workout_duration of 1 minute.
Results:
user_id| workout_id| post_id| exercise_id| number_of_reps|
-------+-----------+--------+------------+---------------+
123| 1 | 1| 1 | 18|
123| 1 | 1| 2 | 29|
123| 1 | 2| 1 | 15|
123| 1 | 2| 2 | 30|
123| 1 | 3| 1 | 20|
123| 1 | 3| 2 | 28|
-------+-----------+--------+------------+---------------+
Workouts:
workout_id| workout_duration|
----------+-----------------+
1| 1|
I tried to retrieve the total number of minutes based on the query below, but it is returning a sum of 6 when I want it to return a value of 3...I think this is because the SUM is not taking into account DISTINCT post_ids...rather it is just summing all post_ids.
@user = User.find(current_user)
@total_minutes = @user.results.includes(:workout).select(:post_id).distinct.sum(:workout_duration)
I have searched high and low for solutions to no avail...any ideas?
Aucun commentaire:
Enregistrer un commentaire