Scopes are awesome (stop writing SQL)

scopes.md

Stop writing SQL

Refresher

named_scope in 2.x.

named_scope :active, :conditions => { :status => "activo" }

Just scope now.

scope :active, where( :status => "activo" )
....
Student.active

Scopes were cool in 2.x, but in 3.x they are the shit.

Arel

Baked into ActiveRecord. Thus, awesome.

$sql = "SELECT * FROM alumno WHERE status = 'activo' AND nombre = "Juan" AND paterno = "Diaz" AND created < '2011-01-01 00:00:00'"

NO MORE SQL

We hates it, precious!

Student.where( :status => 'activo' ).where( :nombre => "Juan", :paterno => "Diaz" ).where( "fecha_registro >= ?", Date.civil( 2011, 1, 1 ) )

(Okay, a tiny bit of SQL)

But wait

Active students? You aren't going to be the only one using that.

scope :active, where( :status => "activo" )
....
Student.active.where( :nombre => "Juan", :paterno => "Diaz" ).where( "fecha_registro >= ?", Date.civil( 2011, 1, 1 ) )

There's more

Juan Diaz? We are always looking for those guys.

Shh, it's a contrived example.

scope :active, where( :status => "activo" )
scope :juans, where( :nombre => "Juan", :paterno => "Diaz" )
....
Student.active.juans.where( "fecha_registro >= ?", Date.civil( 2011, 1, 1 ) )

Not done

Before or after a certain date is super handy. But which date?

Take input!

scope :active, where( :status => "activo" )
scope :juans, where( :nombre => "Juan", :paterno => "Diaz" )
scope :registered_on_or_after, lambda { |date|
  where( "fecha_registro >= ?", date )
}
....
Student.active.juans.registered_on_or_after Date.civil( 2011, 1, 1 )

Don't stop!

Oops! Now we want to do lots of stuff with students who recently registered. Let's say... in the past two weeks.

scope :active, where( :status => "activo" )
scope :juans, where( :nombre => "Juan", :paterno => "Diaz" )
scope :registered_on_or_after, lambda { |date|
  where( "fecha_registro >= ?", date )
}
scope :recent, lambda {
  registered_on_or_after DateTime.now - 14
}
....
Student.active.juans.recent

Aw, yeah.

Required reading

Go read this. Essential.

Don't read it right now, we're in a meeting. Silly.

When to use scopes?

Jason's rule: "a scope should be an adjective"

Adjectives are describing words: "spotted", "fuzzy", "happy"

Teardown

c11a116eca640da8ac2245f39baa61077fe57a20

Start reading SQL

But I don't like scopes!

I like to write everything by hand because it's the most efficient which is why I do all my projects in assembly.

I can't execute all these queries! I need speed!

The secret

There is only one query.

irb(main):138:0> Student.where( :nombre => "Juan" ).count
=> 1
irb(main):140:0> Student.where( :nombre => "Juan" ).collect { |s| s.id }
=> [336660]
irb(main):137:0> Student.where( :nombre => "Juan" ).class
=> ActiveRecord::Relation

Wait, what?

Arel doesn't execute the query until you start demanding results.

So you can chain to your heart's content, and nothing hits MySQL until you use to_a or each or [] or something.

Freakin' magic

students = Student.recent
students = students.active if params[ :only_active ]

def self.find_those_juans
  Student.juans
end
...
Student.find_those_juans.active

Even more tuning

Use includes to "preload" an associated model

Student.active.includes( :guardian ).each do |s|
  puts s.guardian.full_name
end

Use joins to use another table in your query

School.find( 9 ).students.active.joins( :guardian ).where( "cliente.codigo_postal" => "22420" )

Watch that log

School.find( 9 ).students.active.sort_by_grade
  #=> SELECT `escuela`.* FROM `escuela` WHERE `escuela`.`escuelaid` = 9 LIMIT 1
  #=> SELECT `alumno`.* FROM `alumno` WHERE `alumno`.`status_alumno` = 'activo' AND (`alumno`.escuelaid = 9) ORDER BY alumno.gradoid
Student.active.count
  #=> SELECT COUNT(*) FROM `alumno` WHERE `alumno`.`status_alumno` = 'activo'
Student.active.first
  #=> SELECT `alumno`.* FROM `alumno` WHERE `alumno`.`status_alumno` = 'activo' LIMIT 1

Or, you can just use to_sql().

Bonus tangent: log-watching is great for tuning

dfc28e081514073c058e1777a835fa860df60626