Querying with Aggregations

Upcase
1
This is a companion discussion topic for the original entry at https://thoughtbot.com/upcase/videos/advanced-querying-aggregations.
5

I am trying to apply what I’ve learned in this trail to one of the projects that I’m working, but am a little stuck. Here are my models:

class Climb < ActiveRecord::Base
  belongs_to :grade
end

class Grade < ActiveRecord::Base
  has_many :climbs
  belongs_to :bucket
  # also has a sequence_number column which is an integer
end

class Bucket < ActiveRecord::Base
  has_many :grades
  # has a name column (string)

  # the "order" of the buckets is determined via the sequence
  # numbers of the grades in that bucket
  def self.ordered
    joins(:grades).
      group('buckets.id').
      select('buckets.*', 'min(grades.sequence_number) as sn').
      order('sn')
  end
end

Given a relation called @climbs, I want to build an array that lists the bucket names, and then a count of how many climbs are in each bucket (but only counting climbs in the given relation, not all of the climbs in the db).

I have that part working, but I also need to have the buckets ordered, as is described in Bucket.ordered.

If I do this:

      Bucket.from(@grade_system.buckets.ordered, :buckets).
      joins(<<-SQL).
        LEFT JOIN (#{climbs_grades.to_sql}) climbs_grades
        ON buckets.id = climbs_grades.bucket_id
      SQL

  def climbs_grades
    @grade_system.grades.joins(<<-SQL)
      INNER JOIN (#{@climbs.to_sql}) climbs
      ON grades.id = climbs.grade_id
    SQL
  end

then the buckets are ordered correctly (which I can tell by adding .map(&:name) onto the end of that). But once I add the group and count, the ordering gets messed up. Here is the full thing:

      Bucket.from(@grade_system.buckets.ordered, :buckets).
      joins(<<-SQL).
        LEFT JOIN (#{climbs_grades.to_sql}) climbs_grades
        ON buckets.id = climbs_grades.bucket_id
      SQL
      group('buckets.name').
      count('climbs_grades.id')
6

I was doing a little more reading, and realized that an order applied in a subquery isn’t guaranteed to be maintained once the outer query is performed.

This alternative works:

@grade_system.buckets.
joins(<<-SQL).
  LEFT JOIN(#{climbs_grades.to_sql}) climbs_grades
  ON buckets.id = climbs_grades.bucket_id
SQL
group('buckets.name').
order('MIN(climbs_grades.sequence_number').
count('climbs_grades.id')

def climbs_grades
  @grade_system.grades.
  select('grades.bucket_id, grades.sequence_number, climbs.*').
  joins(<<-SQL)
    LEFT JOIN (#{@climbs.to_sql}) climbs
    ON grades.id = climbs.grade_id
  SQL
end

But it still would be nice to be able to the ordering scopes that are already defined…

7

SQL error driven development

I laughed too hard at this.