We use the following domain model in our examples:
Basically, Person and Company belong to a Country, and Person and Company have a many-to-many relationship between them, which is stored in the relationship table CompanyPerson.
The #activerecord 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 #to_sql.
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:
We may also use arrays of desirable values instead of single values. That will generate queries that use the IN SQL keyword:
Additionally to arrays, ranges are also accepted as value predicates:
You can also have endless ranges:
which now with Rails 6 can simply be written like:
It's possible to constraint our queries to several simultaneous predicates. We can either provide #where an hash with several keywords or we can chain several invocations of #where:
If otherwise we wish to compose our predicates with an OR we may use #or.
When we are searching just for a specific row we may use #find_by instead, which stops searching once it finds the first match by using LIMIT 1
Or we may provide our own custom limit by chaining #limit to our #where queries:
It's possible to get the records not matching a certain condition by using #not together with #where:
It's possible to use #order to sort the results is ascending or decending order:
There are several functions that allow folding a query result into certain values, such as #count, #maximum, #minimum and #average:
Additionally, it's possible to group elements by a given column and fold these elements with those folding functions, thus performing GROUP_BY queries:
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 1 + N queries problem and the following interaction makes it evident:
As we see, a new query is performed each time we iterate a person to access its country.
#includes
#includes enables us to eager load all the data at once, thus optimizing what is performed above. It behaves as a LEFT OUTER JOIN, thus loading all the entries of the related model that concern our main model entries.
But, wait, that is not a LEFT OUTER JOIN - you might say! Well, as there are no additional constraints on the countries we are selecting, Rails prefers to resort to a WHERE id IN query, taking advantage of the primary key index. Though, if we add constraints to our query active record performs a LEFT OUTER JOIN 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:
And it is also possible to do so in a nested fashion, by passing a relationships hash instead:
#joins
Sometimes we are only interested in the records that are associated with the related module. When this is the case, we use #joins, which has the semantics of an INNER JOIN.
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:
If we are only interested in records that are associated with a related model with specific attributes values, we can use #joins and then #where to specify constraints on our models.
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 ActiveRecord::Base#scope class method to create such reusable queries. You are able to attain the same functionality by simply defining class methods:
After defining the queries in such fashion, you are able to chain these high level abstractions together.
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.
When creating Arel queries one usually starts by getting the Arel::Table objects of the SQL tables we want to interact with:
We can access the collums of our tables like an hash. From these tables one is able to define predicates (Arel::Node) invoking methods on its attributes, which are accessible through the method []:
The predicates #eq, #not_eq, #lt, #gt, #lteq, #gteq are equivalent to the operators =, !=, <, >, <=, >= and work like this:
The #and & #or can be used to create multitable predicates and work in a similar way, like this:
We can later pass these predicates to #where, and as long as the required tables are joined the predicate will be successfully converted to SQL:
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 #matches Arel predicate with the % SQL wildcard:
This returns people whose last_name start with the character "A".
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.
Rails developer with 10+ years of experience with diverse technologies. I am interested in functional programming.
Computer science student and ImaginaryCloud part-timer. Eager to learn new technologies and techniques. Tennis and piano player.
People who read this post, also found these interesting:
.png)
