3

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?

EDIT: Here is the generated SQL from the query above:

SELECT DISTINCT SUM(workout_duration) 
FROM "results" 
LEFT OUTER JOIN "workouts" ON "workouts"."id" = "results"."workout_id" 
WHERE "results"."user_id" = ?  [["user_id", 123]]
brianyates
  • 399
  • 3
  • 16
  • 1
    Do you have the generated sql? – Eric Duminil Dec 21 '16 at 19:01
  • Hi @EricDuminil, I just made a quick edit and added the generated SQL. – brianyates Dec 21 '16 at 19:11
  • `SELECT DISTINCT SUM(workout_duration)` looks wrong. I don't know how to get it in one rails query. You could get a list of `workouts` based on distinct `post_id` first, and sum duration after. – Eric Duminil Dec 21 '16 at 19:24
  • I just tried `@distinct_posts = @user.results.joins(:workout).select(:user_id, :post_id).distinct` and then `@minute_total = @distinct_posts.sum(:workout_length)` and it generated basically the same SQL. I might just defer to using raw SQL but I feel like that query would take longer to execute than with the nifty rails functions... – brianyates Dec 21 '16 at 19:52
  • Yes, you'd need a `.to_a` after the first query. – Eric Duminil Dec 21 '16 at 19:57
  • I think it might be most efficient to just grab my results via raw SQL, which I achieved. However, right now my `ActiveRecord::Base.connection.execute(sql_query)` query is returning `[{"SUM(workout_duration)"=>3, 0=>3}]`...I feel dumb for not being able to properly extract the sum, but I can't seem to figure that out...how do I get it to just spit out "3" rather than that hash? – brianyates Dec 21 '16 at 21:28
  • Well I think I just solved my problem by doing `ActiveRecord::Base.connection.execute(sql_query)[0][0]`...I just feel weird about doing that...I think I'm going to go ahead and mark this as solved. – brianyates Dec 21 '16 at 21:32

1 Answers1

0

I solved this by using raw SQL:

 @minute_total = ActiveRecord::Base.connection.execute(minute_query)[0][0]

private
def minute_query
"SELECT SUM(workout_duration) 
FROM (SELECT DISTINCT(results.post_id), results.user_id, workouts.workout_duration 
FROM results LEFT OUTER JOIN workouts ON results.workout_id = workouts.id   
WHERE results.user_id = #{@user.id})"
end
brianyates
  • 399
  • 3
  • 16