My 3 Items For Today's Lunch & Learn With Arel

Arel.md

Before We Beging

This is ActiveRecord's Relation::QueryMethods implementation where most SQL is built.

• Will talk about:
  • collapse_wheres
  • reverse_order_value

def build_arel
  arel = table.from table
  build_joins(arel, @joins_values) unless @joins_values.empty?
  collapse_wheres(arel, (@where_values - ['']).uniq)
  arel.having(*@having_values.uniq.reject{|h| h.blank?}) unless @having_values.empty?
  arel.take(connection.sanitize_limit(@limit_value)) if @limit_value
  arel.skip(@offset_value.to_i) if @offset_value
  arel.group(*@group_values.uniq.reject{|g| g.blank?}) unless @group_values.empty?
  order = @order_values
  order = reverse_sql_order(order) if @reverse_order_value
  arel.order(*order.uniq.reject{|o| o.blank?}) unless order.empty?
  build_select(arel, @select_values.uniq)
  arel.distinct(@uniq_value)
  arel.from(@from_value) if @from_value
  arel.lock(@lock_value) if @lock_value
  arel
end

take

• Take is basically the synoymn for LIMIT.

orders = Order.arel_table
orders.take(2).to_sql # => "SELECT  FROM `orders`  LIMIT 2"

• Cant think of a way to use this.
• FYI, skip is the arel method that mirrors OFFSET.
• Just use ActiveRecord limit(n) method.

where

• Allows you to add a condition/expression to the AST (tree).
• Think of this as pushing an item to an array since there can be multiple wheres.

>> orders = Order.arel_table
>> exp    = orders[:id].eq(53332)
>> exp.to_sql # => "`orders`.`id` = 53332"
>> exp.class  # => Arel::Nodes::Equality

>> orders.where(exp).to_sql
=> "SELECT FROM `orders`  WHERE `orders`.`id` = 53332"

Let's explore SQL injection and what Arel provides for our safety.

orders = Order.arel_table

>> param = "22"
>> exp   = "id = #{param}"
>> orders.where(exp).to_sql
=> "SELECT FROM `orders`  WHERE 'id = 22'"

>> param = "22 ;DROP TABLE orders"
>> exp   = "id = #{param}"
>> orders.where(exp).to_sql
=> "SELECT FROM `orders`  WHERE 'id = 22 ;DROP TABLE orders'"

• This is ActiveRecord's Relation::QueryMethods does with all the wheres.
• Notice how it wraps up all the equalities with an AND and puts them last.

def collapse_wheres(arel, wheres)
  equalities = wheres.grep(Arel::Nodes::Equality)
  arel.where(Arel::Nodes::And.new(equalities)) unless equalities.empty?
  (wheres - equalities).each do |where|
    where = Arel.sql(where) if String === where
    arel.where(Arel::Nodes::Grouping.new(where))
  end
end

reverse_order

• Is a Rails API on top of Arel via the Relation class. Never confuse Relation with Arel.
• Here is the source code.
  • Clones the relation.
  • Then sets a boolean on the new clone and returns it.
• Usage:

>> Order.order("id DESC").to_sql
=> "SELECT `orders`.* FROM `orders`  ORDER BY id DESC"
>> Order.order("id DESC").reverse_order.to_sql
=> "SELECT `orders`.* FROM `orders`  ORDER BY id ASC"

>> orders = Order.arel_table
>> ast    = orders.order(:id,:desc)
=> #<Arel::SelectManager:0x007fc1a56e1798 ... >

• How it is implemented.

def reverse_order
  relation = clone
  relation.reverse_order_value = !relation.reverse_order_value
  relation
end

def build_arel
  # ...
  order = @order_values
  order = reverse_sql_order(order) if @reverse_order_value
  # ...
end

• This method is REALLY janky.
• Easy if object is an Ordering node.
• Breaks strings up

def reverse_sql_order(order_query)
  order_query = ["#{quoted_table_name}.#{quoted_primary_key} ASC"] if order_query.empty?
  order_query.map do |o|
    case o
    when Arel::Nodes::Ordering
      o.reverse
    when String, Symbol
      o.to_s.split(',').collect do |s|
        s.strip!
        s.gsub!(/\sasc\Z/i, ' DESC') || s.gsub!(/\sdesc\Z/i, ' ASC') || s.concat(' DESC')
      end
    else
      o
    end
  end.flatten
end

>> exp = "(CASE WHEN order_state = 'canceled' THEN 0 ELSE 1 END)"
>> Order.order(exp).to_sql 
=> "SELECT `orders`.* FROM `orders`  ORDER BY (CASE WHEN order_state = 'canceled' THEN 0 ELSE 1 END)"
>> Order.order(exp).reverse_order.to_sql 
=> "SELECT `orders`.* FROM `orders`  ORDER BY (CASE WHEN order_state = 'canceled' THEN 0 ELSE 1 END) DESC"

• See what Rails does badly?
• Ordering has been part of Rails security fixes in past.
• I worked with @tenderlove on a few of these with the adapter.
• TL;DR - Both ActiveRecord and Arel do weak ordering implementations and trust. Be careful!

Lighting Talk

• Learn when to use Arel.sql() shorthand to whitelist literal strings.

orders = Order.arel_table
exp1   = 'id = 22'
exp2   = Arel.sql('id = 22')

>> orders.where(exp1).to_sql
=> "SELECT FROM `orders`  WHERE 'id = 22'"
>> orders.where(exp2).to_sql
=> "SELECT FROM `orders`  WHERE id = 22"