The ARQI, which was previously known as Arel, is a special kind of query mapper. In short, you use the active record API to chain together ideas that are translated into SQL. To be effective, you should learn some SQL first. While the basics of SQL are the same for different flavors of SQL (databases), some databases have small differences. The Query interface tries to eliminate those differences to the Rails developers so that you don’t have to think about what database is being used on the backend.
Meet the databases:
pronounced “Postgres,” this is arguably the most popular database on the internet. For learning, it makes a cheap and fast tool that you can quickly get set up with. It boasts some features over its primary competitor, MySQL
MySQL is the rock-solid workhorse of the internet, powering massive data distributions and, arguably, the other most popular database server on the internet. Hello, meet MySQL.
MySQL is fast and performant and is extremely stable, but it is memory-heavy (that means it uses a lot of memory in the cloud), which makes it expensive to run. It also has sophisticated sharding which applies to you if you are a very massive website.
Orcale is an enterprise database provided by Oracle Corporation. Oracle is typically found in larger, corporate environments.
A database you use only for development. This database keeps its data written on a disk file in your Rails app.
This is Microsoft SQL, Microsoft’s flavor of SQL. Sharing many similarities to MySQL, you will find developers use this after they outgrow Microsoft Access. MS SQL can be hosted on Azure, Microsoft’s cloud platform, and is hosted by many other providers as well.
Is another enterprise database. You will note that the company “Sybase” was acquired by SAP and is now called SAP, but the database Sybase lives on and is one of the databases supported by the Rails ORM.
Ok with that overview of what database choices we might have let’s get into what ARQI actually is.
ActiveRecord Query Interface
The methods that you use to query from your base object, or Active Record root object. Each method will create or modify what will eventually be the complete SQL query. When you create the ARQI object, the idea of a query exists in memory as — a query that has yet to be executed. That is, it has not actually run the SQL statement. against the database. It does this to allow you to chain one ARQI-object, or Arel, onto another, thus combining SQL statements.
You will “chain” these methods together, that is, use them one after another with a dot. We call this “dot notation” and it works because instead of executing your SQL immediately, the ActiveRecord relation returns a chainable object.
When you create the ARQI object, the idea of a query exists in memory as — a query that has yet to be executed.
Use find when you know the id of the thing you are searching for. However, when creating new things, you want to use the magic Rails methods
Ffor preventing race conditions within the creation action. Use create_with(locked: false) to tell Active Record to run the query in a locked transaction. If successful, it returns an unlocked AR object.
Here we can pass a distinct flag to the query which will make Active Record return only a single record per unique value.
Here we tell Active Record to eagerly load the association specified. This eager loading by performing a LEFT OUTER JOIN. As discussed in eager_loads’s cousins includes and preload, Rails has two ways of dealing with N+1s: 1) “big join” and 2) “separate queries.” Eager load tells Rails to always use strategy #1 “the big join.”
If you want to specify a non-standard Rails table name.
Allows you to pass ‘GROUP BY’ to your SQL to group records with a matching result together.
Allows you to specify a HAVING clause in your SQL. You will need a
.group as well.
This specifies the relationships that ActiveRecord will find in the resultset. This is useful for when we will need these relationships during view or JSON rendering. This is necessary to avoid N+1 queries. See the post Who Is N+1? for a discussion of N+1 queries.
Rails has two ways of dealing with N+1s: 1) “big join” and 2) “separate queries.” (Between the three choices
includes, typically prefer
includes to start out with then try the other options if you are trying to optimize a big query)
Here we tell Rails to use a join query, which is essentially an inner join. That will return results where Table A and Table B have non-nill keys for one another only. You use this if you are just filtering results – not accessing records from a relationship –
joins is what you want.
joins does not load data from the relationship into memory: accessing columns from the relationship will trigger N+1 queries.
Here we perform a LEFT OUTER JOIN on the data — what you commonly call a LEFT JOIN (showing the “left” tables, which in ActiveRecord is the base of the object we started with).
I hope you’ve enjoyed this brief introduction to Active Record. The most important thing to remember is the “observer-effect” — the Arel object does not actually execute the SQL and “become” an array (really, it is an array-like Arel object), until you do something to observe it— like call,
.collect,you loop through it, you examine it, etc. Sometimes you want to invoke the AR chain, in which case you should use
.all to tell Active Record to invoke the chain and turn it into an array-like object.
ActiveRecord Query Interface is an important tool to use object-relational mapping (ORM) to marry your ActiveRecord object to the SQL that will drive the queries against your database. Continuing from yesterday’s post about ActiveRecord Query Interface, I’ll pick up where I left off.
This is is how you restrict records to show only a block of records. You use limit to specify the number of records to show and offset to specify the number of records to skip. You will note that in MySQL, this translates to
LIMIT [offset_count], row_count
 denotes an optional parameter, that is, if we pass only one parameter, it will be assumed to be the
row_count, not the
After that, the value passed to limit is the row_count. You should combine
offset if you want something other than the inital offset (which is 0), see below.
This is useful when you have a method that wants to return a chainable relation to another method, but this method needs to specify “none” or “no records.”
This is limit’s cousin: the offset. This is the number of records to skip before starting to return the row_count (specified in the limit). For example, in MySQL, this translates to:
LIMIT offset, row_count
To combine limit & offset, use
.limit(10).offset(20). This produces
LIMIT 10, 20
Above all, this tells MySQL to pull 10 records starting at the 21st record returned by the query. (We skip, or offset, the first 20 records.)
This is how you sort your query.
Order takes a symbol or a block. If you pass it a symbol, it will order by that field name, ascending. On the other hand, if you pass the symbol as a hash, like so, you can specify a reverse sort (or ‘descending’)
ORDER BY created_at1
Similarly, with the
ORDER BY created_at DESC
Here we tell rails to load side relationships. Rails has two ways of dealing with N+1s: 1) “big join” and 2) “separate queries.” Unlike
.includes (which uses either of the strategies)
.preload loads the association data using strategy #2 separate query. You might do this because you started with
.includes and you later determine via measurement that the big join strategy is slower than the separate queries strategy so you want to force your app to use strategy #2.
Use this to tell Rails to create ActiveRecord objects that cannot be written to. In other words, this will be a read-only object.
Use this to override the default scope on the model. Therefore, whatever default order is specified, Active Record will now ignore it and use the newly specified scope.
Here the magic of Active Relations exposes itself. Let’s say you define a relation, like
people = Person.select(:age, :name)
However, some logic in your app determines that you need different fields. You can use
reselect to override the existing
select set on your AR chain. To be clear, this will mean your existing
select will not be used.
Use .reverse_order to take the current sort order (or default order) and reverse it.
select(:field1, :filed2, :field3)
This is where you can specify which fields to “hydrate” the object with. You would only use this to limit the default behavior which is to hydrate (or load) all of the fields that ActiveRecord finds.
Here you can specify a condition, like
["age > ?", min_age]
Notice that where typically takes an array, and the question marks (
?) are replaced with the variables you see in positions #2, #3, etc of that array you pass.
In conclusion, Active Record is a sharp tool to construct optimal SQL, but you need to know both sides of the equation.
I hope you’ve enjoyed this brief overview of ActiveRecord.