ActiveRecord is an extremely powerful tool, allowing us as developers to be more productive by writing less and doing more. Most of the time this less works to our advantage, but sometimes less is not more.
Imagine you have the following models:
CREATE TABLE books (
id BIGSERIAL PRIMARY KEY,
title TEXT,
author TEXT
);
CREATE TABLE chapters (
id BIGSERIAL PRIMARY KEY,
book_id BIGINT REFERENCES books(id) NOT NULL,
title TEXT,
rank INTEGER
);class Book < ActiveRecord::Base
has_many :chapters
endclass Chapter < ActiveRecord::Base
belongs_to :book
endNotice that we have a Book model which has title and author attributes,
as well as a Chapter model which references a Book (via book_id) and
title attributes.
Since ActiveRecord provides easy interfaces for querying from our database, we
can quickly find all Books by an author, title, or containing a Chapter.
> Book.where(author: "H. P. Lovecraft")
=> #<ActiveRecord::Relation [#<Book id: 1, title: "Necronomicon", author: "H. P. Lovecraft">, ...]>
> Book.find_by("title = 'Necronomicon'")
=> #<Book id: 1, title: "Necronomicon", author: "H. P. Lovecraft">
> chapter = Chapter.find_by(title: "The Call of Cthulhu")
=> #<Chapter id: 19, book_id: 1, title: "The Call of Cthulhu", rank: 19>
> chapter.book
=> #<Book id: 1, title: "Necronomicon", author: "H. P. Lovecraft">If you were paying close attention to the above statements, you may have noticed that two of the arguments were a hash, but one used a string. ActiveRecord allows developers to pass a SQL string to be executed, among other things. For the most part, passing a string into ActiveRecord’s querying utilities will work as expected…for the most part.
Once we get into more complex queries, ActiveRecord may help us avoid problems
that we’re not always weary of. Notice how both of our Book and Chapter
models have a title attribute, what would happen if we tried to join these
two tables together while querying based on title?
> Book.joins(:chapters).find_by("title = 'The Call of Cthulhu'")
PG::AmbiguousColumn: ERROR: column reference "title" is ambiguous
LINE 1: ...ters" ON "chapters"."book_id" = "books"."id" WHERE (title = '...Ah, an error! It looks like our database did not know which table’s title
attribute we were interested in using. Now lets try the same method, but use a
hash instead.
> Book.joins(:chapters).find_by(title: "The Call of Cthulhu")
=> nilNil? Well, at least this didn’t raise any exceptions. To better understand what happened, let’s take a look at the generated SQL.
SELECT "books".* FROM "books" INNER JOIN "chapters" ON "chapters"."book_id" = "books"."id" WHERE ("books"."title" = 'The Call of Cthulhu') LIMIT 1Near the end of the query, we see that ActiveRecord automagically selected our
Book model to be queried upon. This is very helpful of Rails, but we didn’t
want to query by our Books title, but on our Chapters title. Enter nested
hashes.
> Book.joins(:chapters).find_by(chapters: {title: "The Call of Cthulhu"})
=> #<Book id: 1, title: "Necronomicon", author: "H. P. Lovecraft">Perfect! Now if we take a look at the generated SQL, we’ll see that ActiveRecord
correctly queried by our Chapters table.
SELECT "books".* FROM "books" INNER JOIN "chapters" ON "chapters"."book_id" = "books"."id" WHERE ("chapters"."title" = 'The Call of Cthulhu') LIMIT 1Although this won’t fix all problems with ActiveRecord, knowing is half the battle.
