Ruby on Rails 3 Model Programming

Rails Model Programming

Create a Model Object

member = Member.new(:user_name => "John", :user_age => 38)

Or

member = Member.new do |m|
  m.user_name = "John"
  ...
end

Or

member = Member.new
member.user_name = "John"

Check if a field is present

member.user_name?

Save it to database

member.save

Create and save a record in DB

Member.create(:user_name => 'James')

Create and save an array of models

Member.create([{ :user_name => 'John' }, { :user_name => 'Mary' }])

Creating an Array of new objects using a block, where the block is executed for each object:

Member.create([{ :user_name => 'John' }, { :user_name => 'Mary' }]) do |m|
  # Set extra attributes
  m.banned = false
end

Inserting new Model Data

User enter information in a HTML form

app/views/home/register.html.erb
<% form_tag :action => 'register' do %>
  <p>User name:<%= text_field_tag 'my_name', @name %></p>

  <%= submit_tag 'Change' %>
<% end %>

The Controller processes the request

app/controllers/home_controller.rb
class HomeController < ApplicationController

  def register
    # Read the form field :my_name
    @name = params[:my_name]

    # Insert a new row for table "members" if value is not blank
    if !@name.blank?
       # Set the column named "user_name" to @name
       @member = Member.create({:user_name => @name})
    end
  end
end

View all Model Data

Retrieve all rows

Controller
class HomeController < ApplicationController

  def view
    # Retrieve all rows in "members"
    @members = Member.find(:all)
  end

end

Iterate through each row

Member.all.each do |m|
   ...
end
View
<% @members.each do |member| %>
  <li><%= member.user_name %></li>
<% end %>

Rails Finder Method locating DB data

Call Example Description
find(id1, id2, ...) Member.find(1,2) Find by primary key(s). Raise ActiveRecord::RecordNotFound if one or more id are not found
find([id1, id2]) Member.find([1,4]) Find by primary key(s)
find :all Member.find :all Find all rows. Note: all data will be loaded into memory
find :first Member.find :first Find the first matched row. Return nil if nothing found
find :last Member.find :last Find last row order by id. Return nil if nothing found
find_by_sql Member.find_by_sql("SELECT * FROM members") Use explicit SQL
find_by_col_name Member.find_by_user_name("john") Find first row with user_name="john"
find_all_by_col_name Member.find_all_by_user_name("john") Find all rows with user_name="john"
find_first_by_col_name Member.find_first_by_user_name("john") find :first for col_name
find_last_by_col_name Member.find_last_by_user_name("john") find :last for col_name
find_by_col1_and_col2_and_col3 Member.find_by_user_name_and_premium_and_age("John", true, 21) finder with multiple columns
find_or_create_by_col_name Member.find_or_create_by_user_name("John") Find a row if exists, otherwise insert one to the DB
find_or_initialize_by_name Member.find_or_initialize_by_user_name("John") Find a row if exists, otherwise "new" a model object

The calls return an array if there are more than 1 row, otherwise, a single model object

find options

Options Description
:conditions Add condition clause
:order Order by
:group Group by
:having SQL Having
:limit SQL Limit
:offset SQL Offset
:joins Table join
:include Eager loading
:select SQL select clause
:from Override table name
:readonly Mark data read only
:lock Acquire a lock
:first First match
:all All match
:last Last match

Finder method options example

@orders = Order.find(:all, :conditions => ["customer_id = ?", params[:customer_id]], :offset => 100,
          :limit => 200, :include=>[{:items => :comments}])

@orders = Order.find(:last, :conditions => [
    "name = :name AND price > :price AND created_at > :order_date",
    { :name => "order name", :price => 10, :order_date => '2010-03-01' }
  ])

Order.find(:all, :conditions => { :ame => "order name", :created_at => '2010-03-01' })

find_each & find_in_batches

Member.all loads all rows into memory which may not be feasible for large table

"find_each" fetches 1000 rows (default) at a time

Member.find_each do | member |
   member.user_name
   ...
end

Fetch 2000 rows at a time

Member.find_each(:batch_size => 2000) do |member|
   ...
end

Fetch 2000 rows at a time and start from id=100

Member.find_each(:batch_size => 2000, :start => 100) do |member|
   ...
end
  • Option :order and :limit are not supported in find_each

Instead of processing 1 member at a time inside the code block, process an array contain a batch of members

Member.find_in_batches do |members|
   # members is an array contain a max of 1000 member
   ...
   members[0].user_name
   ...
end

Delete Data

destroy_all

destroy_all "created_at < '2010-03-01'"
  • Deletes the data by instantiating each record and then calling the destroy method
  • Will execute a cascade delete
  • Deleting N rows will take N select SQL and N delete SQL

delete_all

delete_all "created_at < '2010-03-01'"
  • Deletes the data without instantiating an ActiveRecord object
  • Will not call the destroy method and therefore will not execute the cascade delete
  • But it takes only 1 SQL for all rows and much efficient that destroy_all

records matching conditions without instantiating the records first, and hence not calling the destroy method and invoking callbacks. This is a single SQL query, much more efficient than destroy_all.

Update

Order.update(12, { :name => 'order name', :comment => 'Express' })

orders = { 12 => { "name" => "order name" }, 13 => { "name" => "second order" } }
Order.update(orders.keys, order.values)

Other Finder methods

Find a record or insert a new row if it is not found

Member.find_or_create_by_first_name(name)

Instead of instantiate a model object like find_by_sql, a hash object is returned with connection.select_all

hash_result = Member.connection.select_all("SELECT * FROM members WHERE id = '1'")
hash_result[:name]
Member.find_by_sql ["SELECT * FROM members WHERE name = ? AND created_at > ?", @name, @order_date]

Rails Model Conditions (WHERE)

Array Conditions

Limit the result by a where clause

Member.where("user_name = ?", name)
  • It represents the where clause of the SQL statement
  • Use '?' for Prepared SQL statement which is more scalable and to prevent SQL injection

Rails where clause with multiple parameters

Member.where("user_name = ? AND age = ?", name, 21)

Use name placeholder instead of '?' for better code usability

Member.where("user_name = :p_name AND age = p_age",{:p_name => name, p_age => 21})

Range condition

Member.where(:age => 21..30)

With other options in where

@orders = Order.where("customer_id = :c_id", {:c_id => 20} , :offset => offset, :limit => 1, :include=>[{:items => :item_comments}])

Using Hash condition

Member.where(:suspend => false, :age => 21 )

Member.where(:age => 21..30)

Member.where(:age => [30,40,50])
  • Only equality, range and subset is allowed in Hash conditions
  • :age => [21..60] The conditions expand to age IN (21,22,...,60), use :age => 21..60 instead

From a Hash

Member.where(params[:member])

Where clause in Join table

Group.includes([:members, :contacts]).where(['members.banned = ?', false]).all

Rails SQL Statement

Rails SQL select

Select specific DB column instead of select * from ...

Member.select("name, age")

Find unique rows

Member.select("DISTINCT(name)")

Apply SQL functions to DB column in Rails

Member.select("MAX(age)")

Rails SQL order

Order result set by column

    @members = Member.order("user_name")

Order result set by multiple columns

    @members = Member.order("user_name", "age DESC")

Rails SQL Limit & Offset

select ... LIMIT 10

Member.limit(10)

select ... LIMIT 10, 4

Member.limit(10).offset(4)

Rails SQL Group by & Having

Member.group("age").order("age")
Member.group("age").having("age > ?", 21)

Rails SQL Read only

Will throw an exception if data returned is changed and saved back to the DB

d1 = Member.readonly.first
d2 = Member.readonly.all

Locking

Optimistic Locking

Optimistic locking allows concurrent data access but will throw StaleObjectError when data is staled

d1 = Member.find(10)
d2 = Member.find(10)

d1.name = "Jonathan"
d1.save

d2.name = "John"
d2.save # ActiveRecord::StaleObjectError Exception will be thrown
  • "d2.save" above will raise ActiveRecord::StaleObjectError since the data is staled already

To support optimistic locking in Rails, add a column "lock_version"

DB Migration
    change_table :members do |t|
      ...
      t.integer :lock_version, :default => 0
      ...
    end

Pessimistic Locking

For pessimistic locking, concurrent access to the same row will be blocked until the lock is released

Member.transaction do
  d = Member.lock.first               # SELECT ... FOR UPDATE   (ROW LOCK is acquired)
  d.name = 'Jonathan'
  d.save                              # COMMIT and the LOCK will be released
end

To lock the row in specific mode

  d = Member.lock("LOCK IN SHARE MODE").first

Rails Model Functions (ActiveRecord)

Model
class Order < ActiveRecord::Base
...
end

Model are sub-class from ActiveRecord. Check ActiveRecord API for details in model functions

a = Account.new(:user_name => "john", :user_age => 28)
ActiveRecord Method Description
a.new_record? Is "a" exist in the DB?
a.save Save "a" to the DB
Account.create Create an object and save to DB
a.update_attributes Save all the attributes in the input Hash
a.valid? Is the Model data valid
a.invalid? Is the Model data invalid

Many method has a counter part ending with "!"

Difference between "save" and "save!"

  • "save" return false if failed
  • "save!", "find_by_col_name!" raise an exception if failed

Output the value before type casting. For example, a user may enter Thirty as age and age_before_type_case will return the original string without typecast it to integer

member.age_before_type_cast

Rails Aggregate Functions

count function

Member.count
Member.where("user_name = ?", name).count

Member.count(:age)      # Count only if age is not nil

Apply aggregate function on a DB column

Member.average("income")
Member.minimum("income")
Member.maximum("income")
Member.sum("income")

Row Exists?

Check if a record exist

# is id=1 exist
Account.exists?(1)

Account.exists?(1,2,3)
# Same
Account.exists?([1,2,3])

# Add WHERE clause
Account.where("user_name = ?", name).exists?

Table Join

Specify a clause for table join (INNER or OUTER Join)

Member.joins('INNER JOIN phone ON phone.c_id = members.id')

Rails Model to demonstrate table join

class Order < ActiveRecord::Base
  has_many :items
end

class Item < ActiveRecord::Base
  belongs_to :Order
  has_many :parts
  has_many :suppliers
end

class Part < ActiveRecord::Base
  belongs_to :item
  has_one :tag
end

class Tag < ActiveRecord::Base
  belongs_to :part
end

Joining tables

Order.joins(:items)

Produces

SELECT orders.* FROM orders
  INNER JOIN items ON items.order_id = orders.id

Multiple table join

Item.joins(:order, :parts)

Join association

Item.joins(:parts => :tag)

Order.joins(:items => [{:parts => :tag}, :suppliers])

Specify join conditions

price_range = 21..25
Order.joins(:items).where(:items => {:price => price_range})
Member.joins(:groups).where(:groups => { :monitored => false })
Member.joins(:groups).where('groups.monitored' => false )
Group.find :all, :joins => [:members, :special_members]

Eager Loading

The lazy loading (the default) does not load any associated table. Use includes to eager loading associated objects

orders = Order.includes(:item).limit(10)

orders.each do |order|
  p order.shipping.price
end

With includes, the shipping information for all matched orders will be loaded at once

SELECT * FROM orders LIMIT 10
SELECT shipping.* FROM orders
  WHERE (orders.order_id IN (1,2,3,4,5,6,7,8,9,10))

Eager loading with multiple tables

Order.includes(:shipping, :items)
Order.includes(:items => [{:part => :tag}, :shipping]).find(1)

Model Programming

Add new access method into a model using WHERE clause

app/models/member.rb
class Member < ActiveRecord::Base
  def self.adult_user(user_name)
    where("user_name = ? & age > ?", user_name, 21).all
  end
end
app/models/member.rb
class Member < ActiveRecord::Base
  def self.adult_user(user_name)
    where(:user_name = 'John',  :age => 21).all
  end
end

Override Model accessor

To add and/or modify how DB column is accessed

class Member < ActiveRecord::Base

  def member_since=(months)
    # Write the DB data as year
    write_attribute(:member_since, months / 12)
  end

  def member_since
    # Return the DB data as month
    read_attribute(:member_since) * 12
  end
end

Serialize Arrays and Hashes to a DB Text Column

class Member < ActiveRecord::Base
  serialize :profiles
end

Serialize the :profile hash to a text column

member = Member.create(:profiles => { "notice" => "email", "format" => "html" })
profile_hash = Member.find(id).profiles

Limit mass-assignment

An order will be created with all the key/value pairs in the Hash (params[:order]) using mass-assignment

Order.create(params[:order])

To restrict what attribute can be set using mass-assignment

  attr_accessible :order_name, :order_comment
  • Hence price can be changed directly but not using mass-assignment
      order.price = 120