Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Ruby, Rails, ActiveRecord and Arel
# 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><% } %>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.