Tiago Madeira

April 28, 2021

•

Min Read

Queries on Rails - Active Record and Arel

This post was originally posted in July 2018 by Pedro Rolo and updated in June 2020 by Tiago Madeira .

There are several features making Ruby on Rails attractive, though where it really shines is when it comes to getting information from relational databases. Its ORM -Active Record - now offers a very mature interface - striking the right balance between conciseness and power.

In this article I will provide some simple examples of how to extract information from a database. This is not meant to be a comprehensive guide, but rather a set of tools that I've been finding useful in my everyday's work. Also, what I'm presenting below is based on the most recent Rails update (Rails 6).

I'll start by defining a domain model against which I will run my queries:

Our Domain Model

We use the following domain model in our examples:

undefinedBasically, undefined and undefined belong to a undefined, and undefined and undefined have a many-to-many relationship between them, which is stored in the relationship table undefined.

What is ActiveRecord

The undefined is what binds the model in our ruby application with its respective database table. The Active record object comes with a set of methods to help us query database records so theres no need to use raw SQL. For comparation purposes will present the translation of our activerecord queries to sql queries. You can easly do this translation using undefined.

ActiveRecord simple queries

The most simple queries only involve a single table and attempt to get the records that have certain specific values on their columns.

For instance, let's say we want to find who has John as first name:

‍undefined

undefined

We may also use arrays of desirable values instead of single values. That will generate queries that use the undefined SQL keyword:undefined

undefined

Additionally to arrays, ranges are also accepted as value predicates:

undefined

You can also have endless ranges:

undefined

which now with Rails 6 can simply be written like:undefined

undefined

ActiveRecord Composable Queries

It's possible to constraint our queries to several simultaneous predicates. We can either provide undefined an hash with several keywords or we can chain several invocations of undefined:

undefined

If otherwise we wish to compose our predicates with an undefined we may use undefined.

undefined

When we are searching just for a specific row we may use undefined instead, which stops searching once it finds the first match by using undefined

‍undefined

Or we may provide our own custom limit by chaining undefined to our undefined queries:

Negating Predicates

It's possible to get the records not matching a certain condition by using undefined together with undefined:

undefined

Sorting

It's possible to use undefined to sort the results is ascending or decending order:

undefined

Grouping

There are several functions that allow folding a query result into certain values, such as undefined, undefined, undefined and undefined:

undefined

Additionally, it's possible to group elements by a given column and fold these elements with those folding functions, thus performing undefined queries:

undefined

ActiveRecord Joins

The most common use case regarding multiple tables is about eager loading some entities that are related to our selected model, which might be, for instance, iteratively accessed. This problem is commonly known as the undefined and the following interaction makes it evident:

undefined

As we see, a new query is performed each time we iterate a person to access its country.

Left Outer Joins with undefined

undefined enables us to eager load all the data at once, thus optimizing what is performed above. It behaves as a undefined, thus loading all the entries of the related model that concern our main model entries.

undefined

But, wait, that is not a undefined - you might say! Well, as there are no additional constraints on the countries we are selecting, Rails prefers to resort to a undefined query, taking advantage of the primary key index. Though, if we add constraints to our query active record performs a undefined instead:

What's to be noticed in the example above is the use of an hash on the where condition in order to specify constraints on our referenced models.

We may also eager load several relations at once:

undefined

And it is also possible to do so in a nested fashion, by passing a relationships hash instead:

‍undefined

Inner Joins with undefined

Sometimes we are only interested in the records that are associated with the related module. When this is the case, we use undefined, which has the semantics of an undefined.

We illustrate the need for this funcionality in the following example, where we only intend to iterate through people that indeed are associated with a country:

undefined

Quering ActiveRecord associations with specific values

If we are only interested in records that are associated with a related model with specific attributes values, we can use undefined and then undefined to specify constraints on our models.

Reusable query abstractions with class methods

Well, all the queries written so far are pretty long. When they get complicated or are intended to be reused through our code it is convenient to give them names.

In older versions of rails, you should use the undefined class method to create such reusable queries. You are able to attain the same functionality by simply defining class methods:

undefined

After defining the queries in such fashion, you are able to chain these high level abstractions together.

undefined

What is Arel

Arel is a domain-specific-language allowing to express your queries in relational algebra. In past versions of Rails it was rather common to have to resort to Arel in order to accomplish some rather frequently requested functionalities, though nowadays Rails 6's Active Record already covers most of these use cases.

I will start by presenting some of the already presented Rails 6 queries by using Arel instead of plain active record. And later focus on how to write some cases where one would still need Arel.

Arel table

When creating Arel queries one usually starts by getting the undefined objects of the SQL tables we want to interact with:

undefined

We can access the collums of our tables like an hash. From these tables one is able to define predicates (undefined) invoking methods on its attributes, which are accessible through the method undefined:

Arel simple query predicates

The predicates undefined, undefined, undefined, undefined, undefined, undefined are equivalent to the operators =, !=, <, >, <=, >= and work like this:

undefined

The undefined & undefined can be used to create multitable predicates and work in a similar way, like this:

undefined

We can later pass these predicates to undefined, and as long as the required tables are joined the predicate will be successfully converted to SQL:

Arel matches (SQL LIKE)

This is a use case where one still needs to resort to Arel in order to obtain a database independent query. In order to do so one should combine the undefined Arel predicate with the undefined SQL wildcard:

This returns people whose last_name start with the character "A".

Conclusion

Both Arel and Active Record have many more functionalities than the ones I've here presented. However they are more than enough to get started with applications with simple requirements.

In the end, it comes to preference choosing between ActiveRecord query methods or Arel, when doing our everyday queries. I find the ActiveRecord sintaxe easier to read and use, but Arel can be still useful when dealing with complex queries.