on Rails | active record | arel | query builder

Composable Query Builders in Rails with Arel

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 new if 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!

subscribe and never miss a post!
Blog Logo

Sophie DeBenedetto
comments powered by Disqus
comments powered by Disqus