title: Rails, ActiveRecord, complex query with multiple joins on nested models
body: <p>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 <code>workout_set.weight</code> for the specified user and exercise and order it by the weight.</p>
  
  <p><strong>Models</strong> look like so (erroneous fields removed):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So for example, with the following data (assume, the exercise_id is the same):</p>
  
  <pre><code>Steve:
    Workout 1:
       weight: 500
    Workout 2:
       weight: 400
  
  Mark:
    Workout 1:
       weight: 300
    Workout 2:
       weight: 350
  </code></pre>
  
  <p>The expected result set would be:</p>
  
  <pre><code>  Steve's Workout 1
    Mark's Workout 2
  </code></pre>
  
  <p>This is on PostgreSql, so the constraints are stricter than sqLite and MySql.</p>
  
  <p><strong>UPDATE:</strong>
  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:</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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")
  </code></pre>