Ruby, Rails, ActiveRecord and Arel

arel_use.rb

# Welcome to my "arel gist"!
## It started hacky, then as an example, but we want it cleaner.

# ActiveRecord
## This is an ORM: you call Ruby methods and get Ruby objects, it makes SQL and object instantiations for you.
## It's Rails' default, but there are alternatives: https://github.com/Sdogruyol/awesome-ruby#orm
Student.all
Topic.first
Workshop.sum(:hours)

## Blast from the past: you could have this code in earlier Rails versions.
## You might have plenty of SQL strings in your codebase from that time, or just plainly hate ActiveRecord.
Student.find_by_name("Abélard")
Student.find(:all, :conditions => {:extra_hours => true})
Student.where("extra_hours = 1")

# Scopes
## Things changed thanks to huge Open Source work, thanks everyone!
## Rails started to support scopes, which are like "chain-able bits of SQL requests"
## that ActiveRecord puts together... though it didn't always work with joins or orders
Workshop.for_topic('Ruby').reorder(:created_at).pluck(:name)

# Arel
## Then Arel (A Relational Algebra) came: you can really merge conditions and chain calls
## Technically you are mostly building an AST (Abstract Syntax Tree) which is traversed to produce SQL strings
lessons = Lesson.arel_table
workshops = Workshop.arel_table

recent_workshops = Lesson.where(lessons[:year].gteq(2016))
miss_w_workshops = Lesson.joins(:workshop).merge(Workshop.for_teacher('Miss Wormwood'))

## Until now that was only an Arel object: SQL is prepared but not called.
## NOW when you need the results, the SQL is called and you get Ruby object(s)
miss_w_workshops.all
miss_w_workshops.count
miss_w_workshops.limit(10).reorder(workshops[:created_at].desc).pluck(:name)

## ActiveRecord will cast your records in the AR classes, but you can also bind/rename the fields
my_classes = miss_w_workshops.select(lessons[:year].as('y'), lessons[:hours].sum.as('hours'), workshops[:name].as('name'))
"Miss Wormwoods taught: " + my_classes.map{|w| "#{w.hours}h of #{w.name} in #{w.y}"}}.join(", ")


# REPORTING ATTENDANCE
## Here's a real use case
## I don't expect anyone to code like this at first, but you will likely end up that way if it lives long in your codebase.


# FIRST: define what we'll use later
## here, we need the `arel_table`s -- Rails might give us its own convention, someday, for now bear with mine
student_ar = Student.arel_table
lesson_ar = Lesson.arel_table
student_lesson_ar = StudentLesson.arel_table

# SECOND: as your codebase grows you will likely have utility methods
## they would both help code reuse, and to get shorter, more explicit code

## this is a database function, using CONCAT:
## it's only doing "#{firstname} #{lastname}" but in SQL
## this code would very likely be defined in the Student model as a class method
sep     = Arel::Nodes.build_quoted(' ')
student_name = Arel::Nodes::NamedFunction.new('concat',
  [student_ar[:first_name], sep, student_ar[:last_name]])


# THIRD: very custom JOINS conditions
## we need a JOINS on lessons too: but we're doing different things
## since we will add a condition on the join, and use it for different things,
## it's only right to give it another name: that's what the alias does
attendance_ar = Lesson.arel_table.alias('attendance')

class Lesson

  scope :with_attendance, lambda {
    joins(
      lesson_ar.join(attendance_ar, Arel::Nodes::OuterJoin)
      .on(lesson_ar[:field].eq(attendance_ar[:field]).and(student_lesson_ar[:attended].eq(true)))
      .join_sources
    )
  }
  scope :last_schoolyear, lambda {
    where(lesson_ar[:year].gteq(Date.year - (Date.today.month > 7 ? 1 : 2)))
  }
end

# FOURTH: chain the scopes and conditions
Lesson.joins(:student).with_attendance.last_schoolyear.merge(Lesson.for_topic('Math'))

# FIFTH: select the bits of the SQL to make the report
.select([
  student_name.as('student_name'),
  lesson_ar.sum(:hours).as('total_hours'),
  attendance_ar.sum(:hours).as('attendance')
]) # .to_csv

# SIXTH: use that data however I want. If you were in the controller:
@report = _.each{|data| data_res << [ data.student_name, data.total_hours, data.attendance ] }

## then render a view or a CSV/XLS file with your favorite gem(s)

## VIEW
## and in HTML.erb (add headers of course)
<% @report.each{|line| %><tr>
  <td><%= line.student_name %></td>
  <td><%= line.total_hours %></td>
  <td><%= line.attendance %></td>
</tr><% } %>