Querying for the aggregate of the maximum of some associations

Question

Clients may participate in challenges and record their achievements. The results are shown by best score in a league table.

This I’ve modelled by:

class Challenge < ApplicationRecord
  has_many :achievements
  has_many :clients, through: :achievements
end

class Achievement < ApplicationRecord
  belongs_to :challenge
  belongs_to :client
end

class Client < ApplicationRecord
  has_many :achievements
  has_many :challenges, through: :achievements
end

I extract the ordered data with a query within a method, so @challenge.results can be iterated through in a view to produce a league table:

class Challenge < ApplicationRecord
  def results
    sql = "SELECT clients.id, clients.first_name, max(achievements.score) AS max_score
          FROM clients
          INNER JOIN achievements on clients.id = achievements.client_id
          INNER JOIN challenges on achievements.challenge_id = challenges.id
          WHERE challenges.id = #{id}
          GROUP BY clients.id
          ORDER BY max_score DESC;"
      ActiveRecord::Base.connection.exec_query(sql)
  end

This works fine. Challenges however now get more complicated. A challenge can also be the aggregated results of a number of other challenges. For example, there are 4 separate challenges in each of weeks 1-4 and the score for each challenge is the maximum distance cycled in 10 minutes. There is also an overall challenge, however, which is based on the aggregate of the maximum distances cycled in all 4 weeks.

I have modelled this by self-joining the Challenge table and defining main_challenge and sub-challenges, so developed the Challenge class as:

class Challenge < ApplicationRecord
  has_many :achievements
  has_many :clients, through: :achievements
  has_many :sub_challenges, class_name: 'Challenge'
  belongs_to :main_challenge, class_name: 'Challenge', foreign_key: 'challenge_id', optional: true
end

This works fine. All I have to do now is develop a query to extract data for a league table for a main challenge. This is where I struggled and fell back on some fairly ugly Ruby.

class Challenge < ApplicationRecord
  has_many :achievements, dependent: :destroy
  has_many :clients, through: :achievements
  has_many :sub_challenges, class_name: 'Challenge'
  belongs_to :main_challenge, class_name: 'Challenge', foreign_key: 'challenge_id', optional: true

  def results
    if has_sub_challenges?
      sub_challenges.map { |c| c.results } # 1
                    .map {|r| r.to_a} # 2
                    .flatten.group_by {|r| r['id']} # 3
                    .map { |key, value| value.reduce { |acc, h| (acc || {}).merge(h) { |key, oldval, newval| key=='max_score' ? (oldval + newval) : oldval } }} # 4
                    .sort_by {|h| -h['max_score']}
    # 1 array of ActiveRecord::Results
    # 2 array of array of hashes [ [...], [{"id"=>41, "first_name"=>"Anne", "max_score"=>3850}, {"id"=>209, "first_name"=>"Aakash", "max_score"=>2500}],...]
    # 3 hash of clients results {41=>[{"id"=>41, "first_name"=>"Anne", "max_score"=>3901}, {"id"=>41, "first_name"=>"Anne", "max_score"=>3850}], 209=>[...],...}    
    # 4 reduce each array of hashes to a single hash, showing the aggregated client score
    else
      sub_challenge_results
    end
  end

  def sub_challenge_results
    sql = "SELECT clients.id, clients.first_name, max(achievements.score) as max_score
          FROM clients
          INNER JOIN achievements on clients.id = achievements.client_id
          INNER JOIN challenges on achievements.challenge_id = challenges.id
          WHERE challenges.id = #{id}
          GROUP BY clients.id
          ORDER BY max_score DESC;"

      ActiveRecord::Base.connection.exec_query(sql)
  end

  def has_sub_challenges?
    return true unless sub_challenges.empty?

    false
  end
end

@challenge.results now works for both main_challenges and sub_challenges, but I would prefer to perform the entire query in both cases directly on the database. How can I directly query on the database when the challenge is a main_challenge?

Update: responding to @Ponder Stibbons comment. For clarity, example Challenge table would look like this:

id	name	challenge_id
1	Barbell Squat	null
2	Cycle Challenge	null
3	Cycle Week 1	2
4	Cycle Week 2	2
5	Cycle Week 3	2

So main challenges have no challenge_id and subchallenges are related to the main challenge through challenge_id.

Update2: clarifying with example input data and desired output.

Achievement table:

id	score	challenge_id	client_id
1	3000	3	1
2	3100	3	1
3	3200	4	1
4	3150	4	1
5	4000	3	2
6	4100	3	2
7	4200	4	2
8	4150	4	2

League table for Cycle Challenge (desired output):

client_id	total metres	rank
2	8300	1
1	6300	2

Answer 1

This I think does what i want. I'd be happy to have suggestions to improve upon it (perhaps some Arel or improved dryness). (I have also added some example data to the dbfiddle Ponder Stibbons established.)

class Challenge < ApplicationRecord
      has_many :achievements
      has_many :clients, through: :achievements
      has_many :sub_challenges, class_name: 'Challenge'
      belongs_to :main_challenge, class_name: 'Challenge', foreign_key: 'challenge_id', optional: true
    
      def results
        if has_sub_challenges?
          main_challenge_results
        else
          sub_challenge_results
        end
      end
      
      def main_challenge_results
        sub_challenge_ids = sub_challenges.pluck(:id).join(',')
        sql = "SELECT unaggregated.id, unaggregated.first_name, sum(max_score) AS sum_max_score
               FROM (
                SELECT clients.id, clients.first_name, max(achievements.score) AS max_score
                FROM clients
                JOIN achievements ON clients.id = achievements.client_id
                WHERE challenge_id IN (#{sub_challenge_ids})
                GROUP BY achievements.challenge_id, clients.id
                ) AS unaggregated
              GROUP BY unaggregated.id, unaggregated.first_name
              ORDER BY sum_max_score DESC;"
    
          ActiveRecord::Base.connection.exec_query(sql)
      end
    
      def sub_challenge_results
        sql = "SELECT clients.id, clients.first_name, max(achievements.score) as max_score
               FROM clients
               INNER JOIN achievements on clients.id = achievements.client_id
               INNER JOIN challenges on achievements.challenge_id = challenges.id
               WHERE challenges.id = #{id}
               GROUP BY clients.id
               ORDER BY max_score DESC;"
    
          ActiveRecord::Base.connection.exec_query(sql)
      end
    
      def has_sub_challenges?
        return true unless sub_challenges.empty?
    
        false
      end
    end