Rails, ActiveRecord, complex query with multiple joins on nested models

Question

I've been trying to create this complex query for some time now to no avail through ActiveRecord. I'm trying to get the workouts that have the highest workout_set.weight for the specified user and exercise and order it by the weight.

Models look like so (erroneous fields removed):

Workout
  belongs_to :user
  has_many :workout_exercises

WorkoutExercises
  belongs_to :workout
  belongs_to :exercise
  has_many :workout_sets

WorkoutSet
  belongs_to :workout_exercise
  weight

So for example, with the following data (assume, the exercise_id is the same):

Steve:
  Workout 1:
     weight: 500
  Workout 2:
     weight: 400

Mark:
  Workout 1:
     weight: 300
  Workout 2:
     weight: 350

The expected result set would be:

  Steve's Workout 1
  Mark's Workout 2

This is on PostgreSql, so the constraints are stricter than sqLite and MySql.

UPDATE: Since I'm running on PostgreSql, the DB is much more strict on the order_by section of the query. Here's the RSpec test with everything written out for the intent of clarity:

    it 'fetches the workout with the highest weight' do
            workout = create(:workout_with_exercises, user: user)
            workout2 = create(:workout_with_exercises, user: user)

            workout.workout_exercises[0].workout_sets[0].weight = 200
            workout.save
            workout2.workout_exercises[0].workout_sets[0].weight = 100
            workout2.save

            expect(user.workouts.count).to eq 2
            exercise = workout.workout_exercises[0]
            max_workout = Workout.joins(workout_exercises: :workout_sets)
                          .where('workout_exercises.exercise_id = ?', exercise.id)
                          .order('workouts.id, workout_sets.weight DESC')
                          .select("workouts.id, workout_sets.weight")
                          .uniq
            #max_workout = user.workouts.max_weight(workout.workout_exercises[0])

            expect(max_workout).to eq [workout]
          end

Which actually throws a # exception. I've tried a bunch of things with this query, but still can't get it to work. I've ended up attempting to do it in straight SQl with the following query (excluding a user.id clause), but I get an empty result set:

max_workout = Workout.find_by_sql("
      SELECT workouts.* 
      FROM workouts,  
        (SELECT DISTINCT workouts.id AS workout_id, workout_sets.weight AS weight
         FROM workouts 
         INNER JOIN workout_exercises ON workout_exercises.workout_id = workouts.id 
         INNER JOIN workout_sets ON workout_sets.workout_exercise_id = workout_exercises.id 
         WHERE workout_exercises.exercise_id = #{exercise.id}
         ORDER BY workouts.id, workout_sets.weight DESC) AS myquery
      WHERE workouts.id = myquery.workout_id")

Great question example to learn from. Great self answer too. — ahnbizcad
– ahnbizcad, Commented Oct 18, 2014 at 6:38
I was wondering perhaps workout set count and weight couldjust be attribute's on workout exercises. This wouldmean each wworkout exercise would have a fixed weight per set. You lose the ability to vary the the weight for a workout set, but if you don't need to vary them, it would simplify your SQL by a lot. — ahnbizcad
– ahnbizcad, Commented Oct 18, 2014 at 6:42

rossta · Accepted Answer · 2013-04-28 23:01:42Z

1

Given an Exercise instance exercise, you can select distinct workouts, nest joins with :workout_exercises and :workout_sets, filter by exercise_id, and order by workout_sets.weight as follows:

Workout.joins(:workout_exercises => :workout_sets).
  where('workout_exercises.exercise_id' => exercise.id).
  order('workout_sets.weight DESC').
  uniq

answered Apr 28, 2013 at 23:01

rossta

11.5k1 gold badge45 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MunkiPhD Over a year ago

Tried, but to no avail. Postgres is strict on the order, which gives too many columns in the select statement, which means IDs cant be selected by default. I've updated my question with your suggestions.

MunkiPhD · Accepted Answer · 2013-05-01 12:38:41Z

After much work and a lot more research, this is the query yielded the desired result set:

WITH joined_table AS (
    SELECT workout_sets.weight AS weight, 
        workouts.user_id AS user_id, 
        workouts.id AS workout_id, 
        workout_sets.id AS workout_set_id,
        workout_exercises.exercise_id AS exercise_id
    FROM workouts 
    INNER JOIN workout_exercises ON workout_exercises.workout_id = workouts.id 
    INNER JOIN workout_sets ON workout_sets.workout_exercise_id = workout_exercises.id       
    ORDER BY workout_sets.weight DESC
    ),

result_set AS (
    SELECT MAX(x.workout_id) AS workout_id, 
           x.user_id, 
           x.weight, 
           x.workout_set_id, 
           x.exercise_id
    FROM joined_table x
    JOIN (SELECT p.user_id, MAX(weight) as weight
        FROM joined_table p
        GROUP BY p.user_id) y 
    ON y.user_id = x.user_id AND y.weight = x.weight
    GROUP BY x.user_id, x.weight, x.workout_set_id, x.exercise_id
    ORDER BY x.weight DESC)

SELECT workouts.*, 
       result_set.weight, 
       result_set.workout_set_id, 
       result_set.exercise_id
FROM workouts, result_set
WHERE workouts.id = result_set.workout_id 
    AND result_set.exercise_id = 1 -- arbitrary exercise ID
    AND workouts.user_id IN (1,2) -- arbitrary set of user IDs

Collectives™ on Stack Overflow

Rails, ActiveRecord, complex query with multiple joins on nested models

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related