Setup
Although it might not be necessary to rebuild the starting app at the start of each episode, I find it useful. I just saw that the each episode’s repo has a before and after app. While I may use this in the future, I want to have muscle memory in creating a basic app up and running. I also just noticed that the before app included seeds.rb to populate the database. I may not want to copy the whole app, I am not above saving myself time tediously creating custom records.
ProductsController
The whole episode put a large focus on the ProductsController. The original index action created the @products instance variable by:
@products = Product.order('name')
getting all the product records and ordering them by area. The problem Eager Loading tries to solve is the following request made in the view.
<%= product.category.name %>
This is commonly known as the O(n + 1) problem. Where the query to get the the product is accompanied by another query to get the product’s category name. I’ve been interviewed about Big O Notation before, so I believe that its a topic worthy of its own post.
Joins
The way I learned about both Inner Join and Outer join was watching schneem’s video about ActiveRecord Joins as part of his open Ruby on Rails course videos. So when we call
`Product.order('category.name').join(:categories)
what is happening is that ActiveRecord is creating a virtual Products_Categories table. Because Product is the model which the methods are being called on, then an array of products are returned. The virtual table joins the product’s category_id with the corresponding category with the matching id. It would look something like this
+-----------------------------------------------+
| Products_Categories (virtual) Table |
+----------------------------------+------------+
|Products | Categories |
+----+------+-------+--------------+----+-------+
| ID | Name | Price | Category_id | ID | Name |
+----+------+-------+--------------+----+-------+
| 12 | Toy | 12.43 | 2 | 2 | Toys |
+----+------+-------+--------------+----+-------+
This is an inner join. The inner join is includes all the records that has a matching category_id. If we created an outer join like this:
'Product.join('LEFT OUTER JOIN categories on products.category_id = categories.id')`
We could select all the records even those that did not have the three requirements needed to be included in an inner join.
- Product must have a
category_id, - Category must have a ID, and
- they must match.
In the case where a product does not have any categories and you wanted to find all those records, you would use a Outer Join. The join method above accepts custom SQL commands. So the SQL command above includes any matches that have the corresponding id and category_id and those that do not. It then takes the products from the left side of the column. To find records where there are no categories associated, you would write the following:
'Product.join('LEFT OUTER JOIN categories on products.category_id = categories.id').where('products: { category_id: nil }')`
The thing I’ve messed up on has been the pluralization of the table names while writing a custom SQL command. I have had times where I left out the s in products and used the singular category instead of the plural categories. The way I need to remember this is by remembering that the tables holds multiple records, thus the table names are always plural.
Select
To continue on with the episode’s lesson, select as used to select what records were to be queried from the database from the ProductsController
@products = Product.order('categories.name').joins(:category).select('products.*, categories.name as category_name')
The select method used here has two arguments being passed in to limit the data returned. The first products.* is a SQL command to return all the products from the database. The second categories.name as category_name returns only the categories’ name. The as category_name aliases the data dynamically within the select method, so the attribute can be referenced from the model.
In the views you can replace product.category.name to product.category_name. In order to be sure that the category_name attribute is always available you have to create a custom getter method within the product model. This way you are not relying on the select method solely.
class Product < ActiveRecord::Base
belongs_to :category
def category_name
read_attribute('category_name') || category.name
end
end
The database is first checked if there is a category_name alias in the database. If there isn’t an aliased category_name, then it fetches the name through the ActiveRecord association.