mercredi 21 décembre 2016

Can't SUM DISTINCT values in Ruby on Rails

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