arel.md

   1 ---
   2 title: Arel
   3 category: Rails
   4 layout: 2017/sheet
   5 ---
   6
   7 ### About
   8 {: .-intro}
   9
  10 Arel is an SQL abstraction library built into Ruby on Rails.
  11
  12 * <https://github.com/rails/arel>
  13
  14 ### Tables
  15
  16 ```rb
  17 users = Arel::Table.new(:users)
  18 users = User.arel_table  # ActiveRecord model
  19 ```
  20
  21 ### Fields
  22
  23 ```rb
  24 users[:name]
  25 users[:id]
  26 ```
  27
  28 ### `where` (restriction)
  29
  30 ```rb
  31 users.where(users[:name].eq('amy'))
  32 # SELECT * FROM users WHERE users.name = 'amy'
  33 ```
  34
  35 ### `select` (projection)
  36
  37 ```rb
  38 users.project(users[:id])
  39 # SELECT users.id FROM users
  40 ```
  41
  42 ### `join`
  43 #### basic join
  44 In ActiveRecord (without Arel), if `:photos` is the name of the association, use `joins`
  45 ```rb
  46 users.joins(:photos)
  47 ```
  48
  49 In Arel, if `photos` is defined as the Arel table,
  50 ```rb
  51 photos = Photo.arel_table
  52 users.join(photos)
  53 users.join(photos, Arel::Nodes::OuterJoin).on(users[:id].eq(photos[:user_id]))
  54 ```
  55
  56 #### join with conditions
  57 ```rb
  58 users.joins(:photos).merge(Photo.where(published: true))
  59 ```
  60
  61 If the simpler version doesn't help and you want to add more SQL statements to it:
  62 ```rb
  63 users.join(
  64    users.join(photos, Arel::Nodes::OuterJoin)
  65    .on(photos[:user_id].eq(users[:id]).and(photos[:published].eq(true)))
  66 )
  67 ```
  68
  69 #### advanced join
  70 multiple `joins` with the same table but different meanings and/or conditions
  71 ```rb
  72 creators = User.arel_table.alias('creators')
  73 updaters = User.arel_table.alias('updaters')
  74 photos = Photo.arel_table
  75
  76 photos_with_credits = photos
  77 .join(photos.join(creators, Arel::Nodes::OuterJoin).on(photos[:created_by_id].eq(creators[:id])))
  78 .join(photos.join(updaters, Arel::Nodes::OuterJoin).on(photos[:assigned_id].eq(updaters[:id])))
  79 .project(photos[:name], photos[:created_at], creators[:name].as('creator'), updaters[:name].as('editor'))
  80
  81 photos_with_credits.to_sql
  82 # => "SELECT `photos`.`name`, `photos`.`created_at`, `creators`.`name` AS creator, `updaters`.`name` AS editor FROM `photos` INNER JOIN (SELECT FROM `photos` LEFT OUTER JOIN `users` `creators` ON `photos`.`created_by_id` = `creators`.`id`) INNER JOIN (SELECT FROM `photos` LEFT OUTER JOIN `users` `updaters` ON `photos`.`updated_by_id` = `updaters`.`id`)"
  83
  84 # after the request is done, you can use the attributes you named
  85 # it's as if every Photo record you got has "creator" and "editor" fields, containing creator name and editor name
  86 photos_with_credits.map{|x|
  87   "#{photo.name} - copyright #{photo.created_at.year} #{photo.creator}, edited by #{photo.editor}"
  88 }.join('; ')
  89 ```
  90
  91 ### `limit` / `offset`
  92
  93 ```rb
  94 users.take(5) # => SELECT * FROM users LIMIT 5
  95 users.skip(4) # => SELECT * FROM users OFFSET 4
  96 ```
  97
  98 ### Aggregates
  99
 100 ```rb
 101 users.project(users[:age].sum) # .average .minimum .maximum
 102 users.project(users[:id].count)
 103 users.project(users[:id].count.as('user_count'))
 104 ```
 105
 106 ### `order`
 107
 108 ```rb
 109 users.order(users[:name])
 110 users.order(users[:name], users[:age].desc)
 111 users.reorder(users[:age])
 112 ```
 113
 114 ### With ActiveRecord
 115
 116 ```rb
 117 User.arel_table
 118 User.where(id: 1).arel
 119 ```
 120
 121 ### Clean code with arel
 122
 123 Most of the clever stuff should be in scopes, e.g. the code above could become:
 124 ```rb
 125 photos_with_credits = Photo.with_creator.with_editor
 126 ```
 127
 128 You can store requests in variables then add SQL segments:
 129 ```rb
 130 all_time      = photos_with_credits.count
 131 this_month    = photos_with_credits.where(photos[:created_at].gteq(Date.today.beginning_of_month))
 132 recent_photos = photos_with_credits.where(photos[:created_at].gteq(Date.today.beginning_of_month)).limit(5)
 133 ```