There’s a matter known as the “N + 1 Queries” problem that has long confused beginning Rails developers to the detriment of their application’s performance. To understand the nature of the issue, let’s consider an example. Suppose your application aggregates information about local arcades, and you’d like to create a listing that outputs the name and state (state in terms of location, as in United States state) of each arcade. In conforming to the standard norms of database normalization, the list of valid states are stored in a table named
states, and the
locations table identifies each arcade’s state using the foreign key
state_id. With this in mind, consider the following seemingly innocent query in which we retrieve ten arcade locations:
@locations = Location.limit 10
In the corresponding application view you then iterate over the retrieved locations like so:
<ul> <% @locations.each do |location| %> <li><%= location.name %> (<%= location.state.name %>)</li> <% end %> </ul>
Pretty innocent bit of code, right? It certainly seems so until you realize these two snippets result in the execution of 11 separate queries! Thus the name “N + 1”, because we’re executing one query to retrieve the ten locations, and then 10 queries to retrieve the name of each location’s state name! To really drive this point home, here’s the eleven queries that executed when I ran the above two snippets:
SELECT `locations`.* FROM `locations` LIMIT 10 State Load (0.2ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 State Load (0.2ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 State Load (0.2ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 State Load (0.2ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 State Load (0.3ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 State Load (0.2ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 State Load (0.1ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 State Load (0.1ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 State Load (0.1ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 State Load (0.4ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 9 LIMIT 1
Rails newcomers often believe explicitly joining the tables will solve the problem:
locations = Location.joins(:state).limit 10 => [#<Location id: 1, name: "Ethyl & Tank", description: ... #<Location id: 10, name: "Atlas Arcade", ... ]
It however does not, because
joins will also forego eager loading:
>> locations.first.state State Load (0.2ms) SELECT `states`.* FROM `states` WHERE `states`.`id` = 36 LIMIT 1 => #<State id: 36, name: "Ohio", ...
Instead, if you know you’re going to want to retrieve associated data, you can eager load the data using the
@locations = Location.includes(:state).limit 10
This produces just two queries, even after iterating over all ten locations just as we did before:
SELECT `locations`.* FROM `locations` LIMIT 10 State Load (4.1ms) SELECT `states`.* FROM `states` WHERE `states`.`id` IN (36, 9)
This time, only two queries are executed. First, the ten locations are retrieved. Next, each location’s state record is retrieved by passing the list of state IDs into the second query. The state information is then attached to each location record, eliminating the need to perform the additional queries!
Rails bundles default 404 (file not found), 422 (unprocessable entity), and 500 (internal server error) pages into every newly generated application. While they get the job done, these pages are pretty bland, so in this post I’ll show you how to update them to suit the design of your application.
The Rails console offers a no-frills interface for experimenting with and peering into all aspects of your application, Active Record objects included. The lack of frills can admittedly become a tad tedious though when peering into larger objects, as demonstrated by this example:
>> g = Game.find(124) Game Load (0.1ms) SELECT `games`.* FROM `games` WHERE `games`.`id` = 124 LIMIT 1 => #<Game id: 124, name: "Zaxxon", description: "A vintage arcade game.", created_at: "2014-07-21 15:42:21", updated_at: "2014-07-21 15:42:21", release_date: 1982, manufacturer_id: 2, slug: "zaxxon">
Fortunately, there’s a fantastic gem called Awesome Print that allows you to have your console cake and eat it too, automatically applying formatting and syntax highlighting to your objects.
In addition to adding user registration and account management features to ArcadeNomad, I’d like to soon give users the ability to comment on games and locations. It would be fun to read nostalgic notes about their favorite 80’s video game and reviews of a recent visit to one of the locations. To implement such a feature, one might presume we need to create separate comment models in order to associate both games and locations with their respective comments, meaning we would separately manage two sets of comments. This approach would however be repetitive because each model would presumably consist of the same data structure. You can eliminate this repetition using a polymorphic association.
It is often useful to know how many records are associated with a model identified as being the parent in a
belongs_to relationship. This information could be used when presenting a list of the U.S. states in a list group, akin to how http://arcadenomad.com/categories works by presenting each category name and the number of associated locations. The easiest way to retrieve this count is by formalizing the other end of the
belongs_to relationship by indicating that a state
class State < ActiveRecord::Base has_many :locations end
With this in place, you can easily determine how many locations are associated with a particular state:
>> state = State.find_by_abbreviation("OH") >> state.locations.size (0.3ms) SELECT COUNT(*) FROM `locations` WHERE `locations`.`state_id` = 36 => 84
However, as you can see by the above example, determining this number requires execution of a
COUNT(*) query, something we’d like to avoid if possible. Using the
counter_cache option, you can! When enabled, this option will update a field in the parent model’s table every time the number of associated records changes, and then whenever
size is called, Active Record will instead retrieve the value found in that field rather than perform the costly
I find the Rails Console to be an indispensable tool, and leave a session open almost constantly throughout the day. It is supremely useful for easily and quickly experimenting with models, queries, and debugging various other data structures such as arrays and hashes. If you’re not familiar with the Rails console, you can enter a new console session by opening a terminal, navigating to your Rails project, and executing
rails console as demonstrated here:
$ rails console Loading development environment (Rails 4.0.0) 2.0.0p247 :001 >
Once you’ve entered the console you’re free to experiment with Ruby snippets, create new Active Record objects, or query those objects, among other things:
location = Location.new => #<Location id: nil, name: nil, description: nil, created_at: nil, updated_at: nil> 2.0.0p247 :002 > Location.count (4.3ms) SELECT COUNT(*) FROM `locations` => 0 2.0.0p247 :003 >
Because I spend so much time inside the console, I prefer to work with a streamlined prompt, and frankly have little use for the Ruby version or command number supplied within the default console prompt. Fortunately, the prompt is easily changed. You can choose to instead see a simplified console prompt consisting of just
>> by creating a file named
.irbrc and place it in your home directory. Inside it, add the following statement:
IRB.conf[:PROMPT_MODE] = :SIMPLE
After saving the file exit and re-enter the console and you’ll be treated to the simplified prompt! After saving the file, exit and enter the console anew to see the streamlined changes:
$ rails console Loading development environment (Rails 4.0.0) >>
While a minor change, reducing the amount of screen clutter gives you more opportunity to focus on what matters!
Like what you read? There’s plenty more where this came from in my new book, “Easy Active Record for Rails Developers”!
Back in the 90’s I lived for a period of several years in southern Italy. When I first arrived, I knew little more than ciao and grazie, meaning learning Italian was a real priority in order to land a job and successfully integrate into society. Within about six months I’d become fairly fluent, although like anybody who is relatively new to a language I was prone to occasionally making the hilarious (to native speakers, anyway) gaffe. The one episode that really sticks out was the day I was grilling out with some friends at a park when I watched a rabbit scamper under some brush. Apparently at the time I must have thought this was a pretty rare event, akin to seeing Bigfoot, because I turned and blurted out, “Ho visto un coglione!” After a moment of confusion, my friends soon broke down into tears laughing, as I had mixed up coglione (testicle) with coniglio (rabbit).
The Definition of Nothing is a Matter of Perspective
It’s similarly easy to confuse terms when working with a programming language, although the consequences will probably not be as comical. When working with Ruby and Rails, one of the most commonplace sources of such foul-ups stems from mixing up the
empty?. All three methods clearly deal with determining whether a data structure is assigned a value, but there are different degrees of nothingness, and in the world of programming those degrees are important. For instance, you might wish to know whether a string is assigned nothing in the sense it has been assigned zero characters, or assigned nothing in the sense it consists solely of whitespace. When examining an object you might like to know whether the object is nil, or simply does not exist (meaning it has not been assigned any value whatsoever of any sort). But if you look up the definition of nil, you’ll learn it means zero, or nothing. So isn’t that what we’re trying to ascertain when we want to know whether a string contains zero characters? Not exactly, and it is precisely these nuances that are the cause of so much confusion when it comes to these methods.
Paginating database results is a standard feature of most web applications, yet isn’t something you’d want to necessarily implement on your own. To do so, you’ll need to first define the desired number of records to be presented per page, and then repeatedly iterate over a portion of the total result set (using
offset()) as the user navigates from one page to the next.
Which reminds me, you’ll also need to create a pagination widget such as that presented below:
Finally, because pagination is likely going to be used throughout a number of different views, you’ll want to bundle the implementation into a reusable solution.
Longtime Rails users are undoubtedly familiar with the
attr_accessible macro. It is used in conjunction with a Rails model to explicitly identify the model attributes that can be set using mass-assignment. For instance, to identify the
zip attributes as mass-assignable, you would set
attr_accessible within the
Location model like this:
class Location attr_accessible :name, :description, :city, :zip end
attr_accessible set like so, I can now conveniently mass-assign these attributes:
location = Location.new( :name => 'Arcade Legacy', :description => 'Offers more than one dozen arcade classics!', :city => 'Cincinnati', :zip => '45240')
zip parameters coming from a properly configured form, assignment is even easier:
location = Location.new(params[:location])
In either case, both approaches are preferable to manually setting each attribute like so:
location = Location.new location.name = 'Arcade Legacy' location.description = 'Offers more than one dozen arcade classics!' location.city = 'Cincinnati' location.zip = '45240'
As is demonstrated by these examples, the
attr_accessible macro is indeed useful. But what if the model included an additional Boolean attribute called
approved that an administrator would use to confirm the submitted arcade was indeed worth of inclusion? If you added
approved to the
attr_accessible list, then a cunning arcade operator could inject
params[:location][:approved] into the POSTed form and bypass the official approval process. At the same time, opting to omit the
approved attribute from the
attr_accessible declaration means you’ll need to resort to a less efficient solution for coding the administrative interface’s
update actions differently (notably, using the latter approach involving manually setting each attribute separately).
Suppose you wanted to highlight an “Arcade of the Day” section on the ArcadeNomad home page. You could do so by randomly retrieving a record from the
games table. While Active Record does not offer a convenience method for retrieving a random record, there are a number of easy approaches one could employ to get the job done.