Composable Query Builders in Rails with Arel
Tags: rails, arel, activerecord, querybuilder
Published: 2017-12-19 18:21:29

---
title:  Composable Query Builders in Rails with Arel
published: true
description: A pattern for implementing a flexible query builder in Rails with Arel.
tags: Rails, Arel, Active Record, Query Builder
---

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](https://www.tunecore.com/) colleague [Andrew Hoglund](https://twitter.com/andrewhoglund). 

## What is Arel?

[Arel](https://github.com/rails/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:

```ruby
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](http://www.rubydoc.info/github/rails/arel/master/Arel/Predications) like:

```ruby
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.

```ruby
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:


```ruby
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:

```ruby
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:

```ruby
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:

```ruby
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:

```ruby
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](https://en.wikiquote.org/wiki/Rick_and_Morty)).

![](http://www.thegreatcodeadventure.com/content/images/2017/12/Screen-Shot-2017-12-13-at-12.14.52-PM.png)<i>You want to build a complex query builder with Arel?? Caaaan dooo!
[source](https://fsmedia.imgix.net/5d/4f/bd/56/9b71/425e/aae8/a38a9d8d5bef/mr-meeseeks-can-get-a-bit-stressed-if-he-lives-for-too-long.jpeg)</i>
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.

<iframe width="560" height="315" src="https://www.youtube.com/embed/6RxMNVfG1qk" frameborder="0" gesture="media" allow="encrypted-media" allowfullscreen></iframe>

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`.

```ruby
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.

```ruby
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:

```ruby
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:

```ruby
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.

```ruby
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:

```ruby
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`.

```ruby
class SceneQueryBuilder
  ...
  def reflect(query)
    self.class.new(scene, query)
  end
```

Lastly, we'll use this method within each sub-query method:

```ruby
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:

```ruby
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:

```ruby
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:

```ruby
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:

```ruby
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:

```ruby
["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:

```ruby
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:

```ruby
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!