We'll use Arel to build a flexible query builder class that will allow us to dynamically search our database given any search form input.
This post focuses less on building intricate Arel queries and more on enacting a design pattern that allows us to leverage Arel in a composeable and flexible manner. This pattern is very much inspired by my TuneCore colleague Andrew Hoglund.
What is Arel?
Arel is a SQL AST (Abstract Syntax Tree-like) manager for Ruby. It allows us to write complex SQL queries in a semantic, reusable fashion. Arel is "framework framework"; it's designed to optimize object and collection modeling over database compatibility. For example, Active Record is built on top of Arel.
Arel maps our data domain into a tree-like structure. For example, we can grab a tree-like representation of the portion of our data model related to users by calling:
User.arel_tabel
=> #<Arel::Table:0x007fd42da94350
@aliases=[],
@columns=nil,
@engine=
User(id: integer, name: string),
@name="user",
@primary_key=nil,
@table_alias=nil>
On our Arel::Table
instance we can execute queries using predicates like:
users = User.arel_table
User.select(users[Arel.star]).where(users[:id].eq(1))
Instances of Arel::Table
can be operated on like a hash whose keys represent each column on the table. Each column is represented as an Arel::Attributes::Attribute
. Arel exposes a set of predicate methods on these attribute objects that we can call to construct our queries.
users = User.arel_table
users[:name]
=> #<struct Arel::Attributes::Attribute
relation=
#<Arel::Table:0x007fd42da94350
@aliases=[],
@columns=nil,
@engine=
User(id: integer, name: string, age: integer),
@name="users",
@primary_key=nil,
@table_alias=nil>,
name=:name>
In order to execute a select statement using one of the predicate methods, gteq
(greater than or equal to) looks like this:
User.select(users[:name]).where(users[:age].gteq(21))
=> SELECT users.name FROM users where users.age >= 21;
Why Do We Need Composability?
You might be thinking––"a tool that allows me to write semantic and reusable queries? Sounds like Active Record". It's absolutley true that Active Record already provides a powerful query tool kit. But what happens when even simple queries stretch the bounds of Active Record's capabilities?
Let's say you want to query your database for user's who are older than 21?
You might write a string of SQL inside your where clause like this:
User.where("age >= 21")
What happens when that query becomes more complex? Let's say you want to query for users who are older than 21 and you want to get all of their associated phone numbers with a particular area code? You might end up with something like this:
User
.select("users.*, phone_numbers.number")
.joins(:phone_numbers)
.where("users.age >= 21 AND phone_numbers.area_code = '212'")
But what if we want to return users who don't have phone numbers? The inner join generated by the .joins
method won't do it. We'll need to make a change:
User
.select("users.*, phone_numbers.number")
.joins("LEFT OUTER JOIN users on phone_numbers where user.id = phone_numbers.user_id")
.where("users.age >= 21")
As our query becomes more and more complex, we have having to use more and more raw SQL. There are a few drawbacks to this approach.
Reusability: We may find ourselves needing to query for users over the age of 21 in other parts of our code, without necessarilly looking for their phone numbers as well. The sub-queries that make up the query above are not reusable in their current form. This approach isn't very DRY.
Readability: I don't know about you, but I am not a fan of reading long strings of SQL, especially in the context of my Ruby classes. Ruby is an eloquent and semantic language, let's use it. Not to mention the fact that we won't get syntax validation for our SQL strings in this context becuase these strings are not being interpreted by the text editor as SQL, but rather as Ruby strings which can pretty much contain anything.
Arel answers both of these concernes for us. It allows us to write even the most complex SQL queries not as strings of SQL, but in Ruby using the Arel DSL. Beyond that though, it allows us to break up large and complex queries into sub-queries that we can re-use again and again.
The same query in Arel is much more semantic and flexible:
users = User.arel_table
phone_numbers = PhoneNumber.arel_table
left_outer_join = users
.join(phone_numbers, Arel::Nodes::OuterJoin)
.on(users[:id].eq(phone_numbers[:user_id]))
.join_sources
User
.select([users[:name], phone_numbers[:number]])
.joins(left_outer_join)
.where(users[:age].gteq(21).and(phone_numbers[:area_code].eq("212")))
Although this may seem verbose at first (I'll admit Arel syntax can be kind of a lot), we can already get a sense of how we might break this down into re-usable components. For example we could abstract out the left_outer_join
composition into a helper method that can construct a left outer join from any associated table. We could could split up the select
statement into its own helper method and build another helper method to contain the query. This is the kind of composibility that we'll create with our own query builder class.
Now that we have a basic understanding of Arel and the advantages it offers, let's use it to build a composeable query builder class in Rails. Our query builder will be able to dynamically search our database given a set of form input.
The App
Our app is pretty simple––a Rick and Morty dialogue generator. The "Meeseeks Box" app has a database full of scenes from Rick and Morty (courtesy of this Wikiquote article).
You want to build a complex query builder with Arel?? Caaaan dooo!
source
Each scene has a season number, episide number and dialogue attribute. Our users can fill out a form with any or all of season, episode or dialogue keyword specified.
We need to build a query builder class capable of searching the scenes table by any or all of these attributes.
The Query Builder
We'll call our query builder SceneQueryBuilder
and define it in app/services/
.
The Base Query
Our query builder will take in an argument of of the Scene
instance generated by our form input and use it to query for _all_scenes that meet the set of conditions. We'll define our base_query
to use Arel to SELECT * FROM scenes
.
class SceneQueryBuilder
attr_reader :query, :scene
def initialize(scene)
@scene = scene
@query = base_query
end
def base_query
Scene.select(scenes[Arel.star])
end
def scenes
Scene.arel_table
end
end
The Sub-Queries
Next up we'll build methods that use Arel to query by each of the attributes that may come in from the form: season, episode or dialogue keyword.
class SceneQueryBuilder
attr_reader :query, :scene
def initialize(scene)
@scene = scene
@query = base_query
end
def base_query
Scene.select(scenes[Arel.start])
end
def scenes
Scene.arel_table
end
def by_season
query.where(scenes[:season].eq(scene.season))
end
def by_episode
query.where(scenes[:episode].eq(scene.episode))
end
def by_dialogue
query.where(scenes[:dialogue].matches(scene.dialogue))
end
end
Now we can use our query builder like this:
scene = Scene.new(scene_params)
SceneQueryBuilder.new(scene).by_season
SceneQueryBuilder.new(scene).by_episode
SceneQueryBuilder.new(scene).by_dialogue
Chaining Query Methods with #reflect
We still can't easily _combine_our queries though. The whole point of our form is to allow our users to search by any combination of these attributes.
Let's give our query builder the ability to chain these query methods together.
We'd like to be able to do the following, with any combination or order of sub-query method invocations:
SceneQueryBuilder.new(scene).by_season.by_episode.by_dialogue
In order for this to work, each sub-query method would have to:
- Return an instance of
SceneQueryBuilder
- Retain awareness of the previous query
Dynamically Composing the Query Builder
We'll define a method #reflect
that will produce a new instance of our query builder class. We'll also modify our class to initialize with a second argument of a query.
class SceneQueryBuilder
attr_reader :query, :scene
def initialize(scene, query=nil)
@scene = scene
@query = query || base_query
end
...
Our modified #initialize
method allows us to pass an optional query in and use this query as the base onto which we are chaining subsequent sub-queries.
Let's break it down:
initial_query = SceneQueryBuilder.new(scene)
second_query = SceneQueryBuilder.new(scene, initial_query).by_season
SceneQueryBuilder.new(scene, second_query)
This has the effect of chaining our sub-queries together. This API is not very elegant if you ask me though.
In order to implement true method chaining, we need to teach each sub-query method to return an instance of the query builder.
We'll define a method, #reflect
, that takes in an argument of the query we are adding to the chain and uses it to initialize a new instance of SceneQueryBuilder
.
class SceneQueryBuilder
...
def reflect(query)
self.class.new(scene, query)
end
Lastly, we'll use this method within each sub-query method:
class SceneQueryBuilder
attr_reader :query, :scene
def initialize(scene)
@scene = scene
@query = base_query
end
def base_query
Scene.select(scenes[Arel.start])
end
def scenes
Scene.arel_table
end
def by_season
reflect(query.where(scenes[:season].eq(scene.season)))
end
def by_episode
reflect(query.where(scenes[:episode].eq(scene.episode)))
end
def by_dialogue
reflect(query.where(scenes[:dialogue].matches(scene.dialogue)))
end
def reflect(query)
self.class.new(scene, query)
end
end
Each sub-query method calls the new query on the current query––stored in the #query
attr_reader––and passes the resulting query into #reflect
. Reflect takes this resulting query and uses it to initialize and return a new instance of SceneQueryBuilder
, in which #query
is now set to the query composed by the sub-query method in question.
Now, we can chain our method invocations like this:
SceneQueryBuilder.new(scene).by_episode.by_dialogue.by_season
Each sub-query method returns an instance of SceneQueryBuilder
, which in turn will respond to the next sub-query method in the chain. Thus, the base query gets added to with each chained method invocation.
Using #inject
To Chain Query Methods
We're almost done with our query builder. But it's not quite as flexible as it could be. We can now chain any combination of sub-query methods togher. But how do we know which methods to invoke? Remember that the scene object we are are passing into our query builder was created with the attributes from our form params:
scene = Scene.new(scene_params)
SceneQueryBuilder.new(scene)
...
The scene object could have any combination of the episode, season or dialogue attributes. We need to invoke the by_episode
method if the scene has an episode
attribute, the by_season
method if the scene has a season
attribute and the by_dialogue
method if the scene has a dialogue
attribute. We could write some conditional logic to accomodate this:
results = SceneQueryBuilder.new(scene)
if scene.episode
results = results.by_episode
end
if scene.season
results = results.by_season
end
if scene.dialogue
results = results.by_dialogue
end
This approach has a few flaws in my opinion.
- It's rigid––we have one hard-coded
if
condition for each attribute. If we add attributes and query methods in the future, we will need to write a newif
conditions. - It's verbose––it doesn't allow us to take advantage of our elegant method chaining on one line.
We need to find a way to dynamically chain these methods depending on the attributes of the scene object.
We'll do two things:
- Identify and collect the sub-query methods that we want to call based on the scene's attributes
- Iterate over these methods using
#inject
We'll wrap up this logic in one public-facing .build_query
method:
attr_reader :query, :scene
def self.build_query(scene)
query_conditions = collect_query_methods(scene)
query_builder = new(scene)
query_conditions.inject(query_builder) { |query_builder, method| query_builder.send(method) }.query
end
def self.collect_query_methods(scene)
scene.attributes.keys.map do |key|
"by_#{key.to_s}" if scene.attributes[key] && !scene.attributes[key].empty?
end.compact
end
end
The .collect_query_methods
method will iterate over the scene's attributes hash. If a given attribute has a value that is not nil
or an empy string, it will collect the corresponding sub-query method name. In other words, if the scene has an episode
attribute, it will add "by_episode"
to the collected array.
As a result, we end up with an array of the sub-query method names that we need to call on our query builder. For example:
["by_season", "by_dialogue"]
Then, we iterate over this collection of method names using #inject
. The #inject
method is an enumerator that takes in an argument of an initial value or object. It iterates over the collection and yields two arguments to the block:
- The collection element that it is currently being iterated over
- The "accumulator", which starts out as the initial value and becomes whatever is returned by the block at the previous step of the iteration
We pass our brand new SceneQueryBuilder
instance into #inject
; tell #inject
to dynamically invoke the given sub-query method via a call to #send
; and pass the result of that method call, i.e. a new SceneQueryBuilder instance with the built-up query, to the next step of the iteration.
The result of the #inject
iteration is a SceneQueryBuilder
instance with a query
attribute that is the result of chaining the by_episode
and by_season
queries:
SceneQueryBuilder.new(scene).by_episode.by_season
We can call #query
on that object to reveal our results.
If we put it all together, we can use our SceneQueryBuilder
super dynamically like this:
scene = Scene.new(scene_params)
SceneQueryBuilder.build_query(scene)
And that's it!
Conclusion
This post focuses less on building complex Arel queries--although I encourage you to take advantage of resources on that topic. The main take-away is the pattern that we established here. We build a query builder class that is totally dynamic--given some search input, it can construct its own queries using the building blocks we established. This pattern becomes even more useful the move complex your search functionality becomes.
Happy coding!
Top comments (3)
I'm a Flatiron grad who was first taught by you in a pilot Bootcamp Prep course, assisted by you during a Hacktoberfest hackathon at the Flatiron campus in Manhattan, and has come across one of your articles every day this week at my RoR developer job that has helped me solve a tough problem. Thank you for contributing to the community and my career in such a meaningful way!
Hey Kayla! Yes I totally remember you!! So thrilled to hear you are working as a dev now :) :) :)
Happy that anything I wrote has been of help. Happy coding!
nice article!