Useful tools to help construct database queries with ActiveRecord and Arel.
gem install arel-helpers
require 'arel-helpers'
Usually you'd reference database columns in Arel via the #arel_table
method on your ActiveRecord models. For example:
class Post < ActiveRecord::Base
...
end
Post.where(Post.arel_table[:id].eq(1))
Typing ".arel_table" over and over again can get pretty old and make constructing queries unnecessarily verbose. Try using the ArelTable
helper to clean things up a bit:
class Post < ActiveRecord::Base
include ArelHelpers::ArelTable
...
end
Post.where(Post[:id].eq(1))
Using pure Arel is one of the only ways to do an outer join with ActiveRecord. For example, let's say we have these two models:
class Post < ActiveRecord::Base
has_many :comments
end
class Comment < ActiveRecord::Base
belongs_to :post
end
A join between posts and comments might look like this:
Post.joins(:comments)
ActiveRecord introspects the association between posts and comments and automatically chooses the right columns to use in the join conditions.
Things start to get messy however if you want to do an outer join instead of the default inner join. Your query might look like this:
Post
.joins(
Post.arel_table.join(Comments.arel_table, Arel::OuterJoin)
.on(Post[:id].eq(Coments[:post_id]))
.join_sources
)
Such verbose. Much code. Very bloat. Wow. We've lost all the awesome association introspection that ActiveRecord would otherwise have given us. Enter ArelHelpers::JoinAssociation
:
include ArelHelpers::JoinAssociation
Post.joins(join_association(Post, :comments, Arel::OuterJoin))
Easy peasy.
JoinAssociation
also allows you to customize the join conditions via a block:
Post.joins(
join_association(Post, :comments, Arel::OuterJoin) do |assoc_name, join_conditions|
join_conditions.and(Post[:author_id].eq(4))
end
)
ArelHelpers also contains a very simple class that's designed to provide a light framework for constructing queries using the builder pattern. For example, let's write a class that encapsulates generating queries for blog posts:
class PostQueryBuilder < ArelHelpers::QueryBuilder
def initialize(query = nil)
# whatever you want your initial query to be
super(query || post.unscoped)
end
def with_title_matching(title)
reflect(
query.where(post[:title].matches("%#{title}%"))
)
end
def with_comments_by(usernames)
reflect(
query
.joins(:comments => :author)
.where(author[:username].in(usernames))
)
end
def since_yesterday
reflect(
query.where(post[:created_at].gteq(Date.yesterday))
)
end
private
def author
Author
end
def post
Post
end
end
The #reflect
method creates a new instance of PostQueryBuilder
, copies the query into it and returns the new query builder instance. This allows you to chain your method calls:
PostQueryBuilder.new
.with_comments_by(['camertron', 'catwithtail'])
.with_title_matching("arel rocks")
.since_yesterday
Requires ActiveRecord >= 3.1.0, <= 4.1.0, tested with Ruby 1.9.3, 2.0.0, and 2.1.0. Depends on SQLite for testing purposes.
bundle exec rspec
- Cameron C. Dutro: http://github.com/camertron