Mastering ActiveRecord and ARel

Dan Shultz

@danshultz • das0118@gmail.com

When I started using rails...

Finders

              Beer.find_by_name_and_abv("Two Hearted Ale", 7.0)

Beer.find_all_by_abv(7.0)

Conditions Hash

               Beer.find(:all, conditions: ["abv < ?", 7])

String Conditions

              Beer.where("abv between ? and ?", 5.5, 7)

Beer.where(abv: 5.5..7)

Then I joined a large team...

We did this...

              
    record = self.handles.first(
      :conditions => {
      :value_type => value.class.name,
      :value_id => value.id
    })

and this...

              Discussion.find(id).comments.joins(:conversation)
  .where("conversations.person_id" => person_id)

and sometimes in our controllers...

              Discussion.where("owner" => person_id, 
  :deleted => false, 
  "created_on > ?" => 2.weeks.ago)

Also this...

              
joins = "INNER JOIN channel_groups cg ON cg.id = discussions.channel_group_id"
joins = "#{joins} INNER JOIN channel_group_people cgp ON cgp.channel_group_id = cg.id" unless as_admin

conditions = "cg.channel_id = #{channel.id} 
  AND discussions.widget_id = #{for_widget_id ? for_widget_id : 'NULL'}"
conditions = "#{conditions} AND cgp.person_id = #{person.id}" unless as_admin

order = "discussions.updated_at DESC"

find(:all, :joins => joins, :conditions => conditions, :order => order, :limit => limit)

and even this...

              stmt = "SELECT DISTINCT
  GROUP_CONCAT(DISTINCT UCASE(CAST(LEFT(AES_DECRYPT(p.last_name, 'xxx'), 1) AS CHAR))) AS letters
FROM channel_groups cg
INNER JOIN channel_group_people cgp ON cgp.channel_group_id = cg.id
INNER JOIN people p ON p.id = cgp.person_id
INNER JOIN channel_membership_attribute_person_values cmapv1 ON p.id = cmapv1.person_id
INNER JOIN channel_membership_attribute_values cmav1 ON cmav1.id = cmapv1.channel_membership_attribute_value_id
INNER JOIN channel_membership_attributes cma1 ON cma1.id = cmav1.channel_membership_attribute_id AND cma1.channel_id = cg.channel_id
INNER JOIN channel_membership_attribute_person_values cmapv2 ON p.id = cmapv2.person_id
INNER JOIN channel_membership_attribute_values cmav2 ON cmav2.id = cmapv2.channel_membership_attribute_value_id
INNER JOIN channel_membership_attributes cma2 ON cma2.id = cmav2.channel_membership_attribute_id AND cma2.channel_id = cg.channel_id
#{if self.show_unregistered_people_in_directory? then
' left join accounts a on a.person_id = p.id'
else
' inner join accounts a on a.person_id = p.id'
end}
WHERE cg.id = #{group_id}"
clause = if (first_attr_id && second_attr_id)
  " AND cmav1.id = #{first_attr_id} AND cmav2.id = #{second_attr_id} GROUP BY cmav1.id, cmav2.id"
elsif (first_attr_id)
...

Stop the Insanity

That last query was used to create an address book style initial list.

[A, G, H, K, S, X]

...it's now this

              people = Person.in_channel_group(group_id)

if !show_unregistered_people_in_directory?
  people = people.registered
end

attribute_ids = [first_attr_id, second_attr_id].compact
if !attribute_ids.empty?
  people = people.with_channel_attribute_ids(attribute_ids)
end

people \
  .order(Person.column_last_initial)
  .uniq \
  .pluck(Person.column_last_initial('last_initial').to_sql)

The Person Model has relationships setup properly

              class Person < ActiveRecord::Base
  has_many :channel_group_people, :dependent => :destroy
  has_many :channel_groups, 
    :through => :channel_group_people 
  has_many :channels, 
    :through => :channel_groups
  has_many :channel_membership_attribute_person_values

  has_many :channel_membership_attribute_values, 
    :through => :channel_membership_attribute_person_values
  has_many :channel_membership_attributes, 
    :through => :channel_membership_attribute_values

  has_one :account
    # ...
end

and scopes created

              class Person < ActiveRecord::Base
  def self.in_channel_group(group_id)
    joins(:channel_groups) \
      .merge(ChannelGroup.by_id(group_id))
  end

  def self.with_channel_attribute_ids(*value)
    joins(:channel_membership_attribute_values) \
      .merge(ChannelMembershipAttributeValue.by_ids(*value))
  end

  def self.registered
    joins(:account)
  end
end

including column reference

              class << self
  alias_method :_, :arel_table

  def column_last_initial(as = nil)
    decrypt = decrypted_function(:last_name)
    left = Arel::Nodes::NamedFunction.new("LEFT", 
      [decrypt, 1])
    Arel::Nodes::NamedFunction.new("UCASE", [left], as)
  end

  protected
  def decrypted_function(column_name, as = nil)
    Arel::Nodes::NamedFunction.new("AES_DECRYPT", 
      [_[column_name.to_sym], 'xxx'], 
      as)
  end
end

And here is the sql

              SELECT DISTINCT UCASE(LEFT(AES_DECRYPT(`people`.`last_name`, x'xxx'), 1)) AS last_initial 
FROM `people` INNER JOIN `channel_group_people` 
  ON `channel_group_people`.`person_id` = `people`.`id` 
INNER JOIN `channel_groups` 
  ON `channel_groups`.`id` = `channel_group_people`.`channel_group_id` 
INNER JOIN `accounts` 
  ON `accounts`.`person_id` = `people`.`id` 
INNER JOIN `channel_membership_attribute_person_values` 
  ON `channel_membership_attribute_person_values`.`person_id` = `people`.`id` 
INNER JOIN `channel_membership_attribute_values` 
  ON `channel_membership_attribute_values`.`id` = `channel_membership_attribute_person_values`.`channel_membership_attribute_value_id`
WHERE `channel_groups`.`id` = 21 
  AND `channel_membership_attribute_values`.`id` = 23 
ORDER BY UCASE(LEFT(AES_DECRYPT(`people`.`last_name`, x'xxx'), 1))
          	
              class Beer < ActiveRecord::Base
  belongs_to :brewery
  belongs_to :style
  has_and_belongs_to_many :beer_drinkers
end

Use "#merge" Religiously

Prevents hard coding table names

              Beer.joins(:brewery).where(brewery: { name: "Bell's" })
# SELECT "beers".* 
# FROM "beers" INNER JOIN "breweries" 
#   ON "breweries"."id" = "beers"."brewery_id" 
# WHERE "brewery"."name" = 'Bell''s'

Beer.joins(:brewery).merge(Brewery.where(name: "Bell's"))
# SELECT "beers".* 
# FROM "beers" INNER JOIN "breweries" 
#   ON "breweries"."id" = "beers"."brewery_id" 
# WHERE "breweries"."name" = 'Bell''s'

Allows you to not duplicate conditions across models

              class Brewery < ActiveRecord::Base
  has_many :beers

  def self.by_name(name)
    where(name: name)
  end
end 

Beer.joins(:brewery).merge(Brewery.by_name("Bell's"))

class Beer < ActiveRecord::Base
  belongs_to :brewery

  def self.by_brewery_name(name)
    joins(:brewery).merge(Brewery.by_name(name))
  end
end

Setup and use Relationships

All Relationships

              class BeerDrinker < ActiveRecord::Base
  has_and_belongs_to_many :beers # many to many
  has_many :breweries, through: :beers # has many through
end

class Beer < ActiveRecord::Base
  belongs_to :brewery # join from BeerDrinker through
  has_and_belongs_to_many :beer_drinkers
end

Inner Joins Through Relationships

              BeerDrinker.joins(:breweries) \
  .merge(Brewery.by_name("Bell's"))

# SELECT "beer_drinkers".* 
# FROM "beer_drinkers" INNER JOIN "beer_drinkers_beers" 
#   ON "beer_drinkers_beers"."beer_drinker_id" 
#       = "beer_drinkers"."id" 
# INNER JOIN "beers" ON "beers"."id" 
#       = "beer_drinkers_beers"."beer_id" 
# INNER JOIN "breweries" ON "breweries"."id" 
#       = "beers"."brewery_id" 
# WHERE "breweries"."name" = 'Bell''s'

Outer Joins Through Relationship

              BeerDrinker.includes(:beers) \
  .merge(Beer.where(id: nil))

# SELECT "beer_drinkers"."id" AS t0_r0, "beers"."id" AS ...
# FROM "beer_drinkers" LEFT OUTER JOIN "beer_drinkers_beers"
#   ON "beer_drinkers_beers"."beer_drinker_id" 
#     = "beer_drinkers"."id" 
# LEFT OUTER JOIN "beers" 
#   ON "beers"."id" = "beer_drinkers_beers"."beer_id" 
# WHERE "beers"."id" IS NULL

Limit Query Interface usage to AR Models

              class Beer < ActiveRecord::Base
  class << self

    def by_name(name)
      where(name: name)
    end

    def by_abv(abv)
      where(abv: abv)
    end

    def by_brewery_name(name)
      joins(:brewery).merge(Brewery.by_name(name))
    end

  end
end 
        		
              Beer.by_abv(5.5..7).by_brewery_name("Founders Brewing Co.")
              
# SELECT "beers".* 
# FROM "beers" INNER JOIN "breweries" 
#   ON "breweries"."id" = "beers"."brewery_id" 
# WHERE "breweries"."name" = 'Founders Brewing Co.' 
#   AND ("beers"."abv" BETWEEN 5.5 AND 7)

Don't Duplicate Logic/Conditions

Limiting to Active Users

              Post.includes(:user).where(users: { is_active: true })

Post.includes(:user).where(users: { is_active: true }) \
  .where("user.email IS NOT NULL") 

Post.includes(:user).where(users: { is_active: true }) \
  .where("users.email IS NOT NULL") \
  .where(users: { banned: false }) 

Post.includes(:user).merge(User.active)

Limiting to Active Users

               class User < ActiveRecord::Base
  has_many :posts
  has_many :documents

  def self.active
    where(is_active: true).with_email.not_banned
  end

  def self.with_email
    where(User.arel_table[:email].not_eq(nil))
  end 

  def self.not_banned
    where(banned: false)
  end 
end

Limiting to Active Users

              class Post < ActiveRecord::Base
  belongs_to :user

  def self.by_active_users
    joins(:user).merge(User.active)
  end
end

class Document < ActiveRecord::Base
  belongs_to :user

  def self.by_active_users
    joins(:user).merge(User.active)
  end
end

When things get complex...

...use Arel

What is Arel

  • Arel is a SQL AST manager for Ruby*
  • Simplifies the generation of complex SQL queries*
  • Adapts to various RDBMS systems*
  • Building block to queries inside ActiveRecord

* https://github.com/rails/arel

Arel Conditions

  • eq - (a = 1)
  • in - (a in [1, 2])
  • matches - (a like "%one%")
  • lt - (a < 1)
  • gt - (a > 1)
  • gteq - (a >= 1)
  • lteq - (a <= 1)
  • condition_all - eq_all - (a = 1 and a = 2)
  • condition_any - eq_any - (a = 1 or a = 2)
  • not_condition - not_eq - (a != 1)
  • not_condition_any - not_eq_any - (a != 1 or a != 2)
  • not_condition_all - not_eq_all - (a != 1 and a != 2)

Accessing Arel From Models

Arel::Table

              > Beer.arel_table.class
 => Arel::Table

> Beer.arel_table[:name].class
 => Arel::Attributes::Attribute

> Beer.where(Beer.arel_table[:name].matches("%ale%"))
  # SELECT "beers".* FROM "beers" 
  # WHERE ("beers"."name" LIKE '%ale%')

Arel::SelectManager

              Beer.scoped.arel.class
 => Arel::SelectManager

query = Beer.where(abv: 5..6)
query.arel.where(Beer.arel_table[:name].matches("%ale%"))

query.all
# SELECT "beers".* FROM "beers" 
# WHERE "beers"."abv" between 5 and 6
#   AND "beers"."name" LIKE '%ale%'

Using Arel for Conditionals

Starts or Ends with

              def starts_or_ends_with(start, ending=nil)
  beer = Beer.arel_table
  ending = ending || start
  where(beer[:name] \
    .matches_any(["%#{ending}", "#{start}%"]))
end
              
Beer.starts_or_ends_with("ale")

# SELECT "beers".* 
# FROM "beers" 
# WHERE (("beers"."name" LIKE '%ale' 
#    OR "beers"."name" LIKE 'ale%'))

Or Conditionals

              def misclassified_ipas
  beer = Beer.arel_table
  style = Style.arel_table

  joins(:style).where(
    beer[:abv].lt(7.5).and(style[:name].eq("Imperial IPA"))
    .or(beer[:abv].gt(7.4).and(style[:name].eq("IPA")))
  )
end

# SELECT "beers".* FROM "beers" 
# INNER JOIN "styles" ON "styles"."id" = "beers"."style_id" 
# WHERE (("beers"."abv" < 7.5 AND "styles"."name" = 'Imperial IPA'
#    OR "beers"."abv" > 7.4 AND "styles"."name" = 'IPA'))

Or Conditionals

              def misclassified_imperials_ipas_and_stouts
  beer = Beer.arel_table
  style = Style.arel_table

  joins(:style).where(
    style[:name].eq("Imperial IPA") \
    .or(style[:name].eq("Imperial Stout")) \
      .and(beer[:abv].lt(7.5))
  )
end

# SELECT "beers".* FROM "beers" 
# INNER JOIN "styles" ON "styles"."id" = "beers"."style_id"
# WHERE (("styles"."name" = 'Imperial IPA' 
#    OR "styles"."name" = 'Imperial Stout') 
#   AND "beers"."abv" < 7.5)

Not Conditions

              class Style < ActiveRecord::Base
  def not_names(*names)
    names = names.flatten
    where(Style.arel_table[:name].not_in(names))
  end
end 

Style.not_names("Stout", "IPA")

# SELECT "styles".* FROM "styles" 
# WHERE ("styles"."name" NOT IN ('Stout', 'IPA'))

Complex Joins with Arel

Inner Join

        			beer = Beer.arel_table
style = Style.arel_table

join_on = beer.create_on(beer[:style_id].eq(style[:id]))
inner_join = beer.create_join(style, join_on)

Beer.joins(inner_join)
# SELECT "beers".* FROM "beers" 
# INNER JOIN "styles" ON "beers"."style_id" = "styles"."id"

Outer Join

        			beer = Beer.arel_table
style = Style.arel_table

join_on = beer.create_on(beer[:style_id].eq(style[:id]))
outer_join = beer.create_join(style, join_on, 
  Arel::Nodes::OuterJoin)

Beer.joins(outer_join)
# SELECT "beers".* FROM "beers" 
# LEFT OUTER JOIN "styles" 
#   ON "beers"."style_id" = "styles"."id"

Multiple Joins

        			beer = Beer.arel_table
beer_drinker = BeerDrinker.arel_table
beer_beer_drinker = Arel::Table.new(:beer_drinkers_beers)

condition = beer.create_on \
  beer[:id].eq(beer_beer_drinker[:beer_id])

first_join = beer.create_join(beer_beer_drinker, condition)


condition = beer.create_on \
  beer_beer_drinker[:beer_drinker_id].eq(beer_drinker[:id])

second_join = beer.create_join(beer_drinker, condition)

Multiple Joins

        			Beer.joins(first_join, second_join)

# SELECT "beers".* 
# FROM "beers" INNER JOIN "beer_drinkers_beers" 
#   ON "beers"."id" = "beer_drinkers_beers"."beer_id" 
# INNER JOIN "beer_drinkers" 
#    ON "beer_drinkers_beers"."beer_drinker_id" = 
#       "beer_drinkers"."id"

User arel for Order by

Just using normal AR style

              Beer.order(:name)
# SELECT "beers".* FROM "beers" ORDER BY name

Beer.joins(:style).order(:name)
# SELECT "beers".* FROM "beers" 
# INNER JOIN "styles" ON "styles"."id" = "beers"."style_id" 
# ORDER BY name

beer = Beer.arel_table
Beer.order(beer[:name])
# SELECT "beers".* FROM "beers" ORDER BY "beers"."name"


Beer.order(beer[:name].desc)
# SELECT "beers".* FROM "beers" ORDER BY "beers"."name" DESC

Named Functions

Arel::Nodes::NamedFunction

#new(name, expr, aliaz = nil)

Named Function

              person = Person.arel_table
decrypt = Arel::Nodes::NamedFunction.new("AES_DECRYPT", 
  [person[:name], 'xxx'])

decrypt.to_sql

# AES_DECRYPT(`people`.`name`, 'xxx')

Combining Named Functions

              person = Person.arel_table

decrypt = Arel::Nodes::NamedFunction.new("AES_DECRYPT", 
  [person[:name], 'xxx'])

left = Arel::Nodes::NamedFunction.new("LEFT", [decrypt, 1])

ucase = Arel::Nodes::NamedFunction.new("UCASE", 
  [left], 'last_initial')

ucase.to_sql

# UCASE(LEFT(AES_DECRYPT(`people`.`name`, 'xxx'), 1)) 
#   AS last_initial

Use the named function

              Person.order(ucase).to_sql
# SELECT `people`.* 
# FROM `people`  
# ORDER BY UCASE(LEFT(AES_DECRYPT(`people`.`name`, 'xxx'), 1))

Person.pluck(ucase.to_sql).to_sql
# SELECT UCASE(LEFT(AES_DECRYPT(`people`.`name`, 'xxx'), 1))
# AS last_initial 
# FROM `people`

Unions

Creating a union

              beer = Beer.arel_table

union = Beer.where(name: "Oberon") \
  .union(Beer.where(name: "Two Hearted")) 

Beer.from(beer.create_table_alias(union, :beers)).all

# SELECT "beers".* 
# FROM ( SELECT "beers".* 
#   FROM "beers" 
#   WHERE "beers"."name" = 'Oberon' 
# UNION 
# SELECT "beers".* 
#   FROM "beers" 
# WHERE "beers"."name" = 'Two Hearted' ) "beers"

Visualizing Arel AST

  • Install Graphviz
  • Install 'ruby-graphviz' gem or add it to your gemfile
              require 'graphviz'
query = Beer.by_abv(5.5..7).by_brewery_name("Bell's") \
  .order("created_at")
graph = GraphViz.parse_string(query.to_dot)
graph.output(:png => "query.png")
Arel Graph

New in Rails 4

Null Object Pattern

          		Beer.none.where(name: "Pale ale")

Not Operator

          		Beer.not.where(name: "Pale ale")

All scopes require callable object

              class Beer < ActiveRecord::Base
  scope :deleted, where(deleted: true)

  scope :deleted, lambda { where(deleted: true) }
end

#update_columns to compliment #update_column

              Beer.first.update_column(:name, "New Name")

Beer.first.update_columns(name: "New Name", abv: 5.5)

* unsafe, bypasses validations and callbacks

Finder methods are deprecated

              Beer.find_all_by_name("Oberon")
              
Beer.where(name: "Oberon")

#pluck takes multiple columns

              > Beer.pluck(:id, :name)
 => [[1, "Hopslam"], [2, "Oberon"], [3, "Two Hearted"]]

Awesome resources

https://github.com/rails/arel

http://benhoskin.gs/2012/07/04/arel-merge-a-hidden-gem

http://erniemiller.org/

http://danshultz.github.com/talks/mastering_activerecord_arel/

https://github.com/danshultz/mastering_active_record_sample_code