9 users = Arel::Table.new(:users)
10 users = User.arel_table # ActiveRecord model
20 ### `where` (restriction)
23 users.where(users[:name].eq('amy'))
24 # SELECT * FROM users WHERE users.name = 'amy'
27 ### `select` (projection)
30 users.project(users[:id])
31 # SELECT users.id FROM users
36 In ActiveRecord (without Arel), if `:photos` is the name of the association, use `joins`
41 In Arel, if `photos` is defined as the Arel table,
43 photos = Photo.arel_table
45 users.join(photos, Arel::Nodes::OuterJoin).on(users[:id].eq(photos[:user_id]))
48 #### join with conditions
50 users.joins(:photos).merge(Photo.where(published: true))
53 If the simpler version doesn't help and you want to add more SQL statements to it:
56 users.join(photos, Arel::Nodes::OuterJoin)
57 .on(photos[:user_id].eq(users[:id]).and(photos[:published].eq(true)))
62 multiple `joins` with the same table but different meanings and/or conditions
64 creators = User.arel_table.alias('creators')
65 updaters = User.arel_table.alias('updaters')
66 photos = Photo.arel_table
68 photos_with_credits = photos
69 .join(photos.join(creators, Arel::Nodes::OuterJoin).on(photos[:created_by_id].eq(creators[:id])))
70 .join(photos.join(updaters, Arel::Nodes::OuterJoin).on(photos[:assigned_id].eq(updaters[:id])))
71 .project(photos[:name], photos[:created_at], creators[:name].as('creator'), updaters[:name].as('editor'))
73 photos_with_credits.to_sql
74 # => "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`)"
76 # after the request is done, you can use the attributes you named
77 # it's as if every Photo record you got has "creator" and "editor" fields, containing creator name and editor name
78 photos_with_credits.map{|x|
79 "#{photo.name} - copyright #{photo.created_at.year} #{photo.creator}, edited by #{photo.editor}"
83 ### `limit` / `offset`
86 users.take(5) # => SELECT * FROM users LIMIT 5
87 users.skip(4) # => SELECT * FROM users OFFSET 4
93 users.project(users[:age].sum) # .average .minimum .maximum
94 users.project(users[:id].count)
95 users.project(users[:id].count.as('user_count'))
101 users.order(users[:name])
102 users.order(users[:name], users[:age].desc)
103 users.reorder(users[:age])
106 ### With ActiveRecord
110 User.where(id: 1).arel
113 ### Clean code with arel
115 Most of the clever stuff should be in scopes, e.g. the code above could become:
117 photos_with_credits = Photo.with_creator.with_editor
120 You can store requests in variables then add SQL segments:
122 all_time = photos_with_credits.count
123 this_month = photos_with_credits.where(photos[:created_at].gteq(Date.today.beginning_of_month))
124 recent_photos = photos_with_credits.where(photos[:created_at].gteq(Date.today.beginning_of_month)).limit(5)
129 * <http://github.com/rails/arel>