ActiveRecord joins are a nifty way to query the database without fumbling over the confusing SQL inner join syntax. Suppose we have a Rails application with User and Doctor models. A user has_one doctor and a doctor belongs_to a user. The schema is a follows:
ActiveRecord::Schema.define(:version => 20130713213355) do create_table "doctors", :force => true do |t| t.string "name" t.string "specialty" t.integer "user_id" t.datetime "created_at", :null => false t.datetime "updated_at", :null => false end create_table "users", :force => true do |t| t.string "email" t.string "password" t.integer "age" t.datetime "created_at", :null => false t.datetime "updated_at", :null => false end end
Write an ActiveRecord query to fetch all doctors that are named “Bob” and are older than 50 years old.
>> Doctor.joins(:user).where("doctors.name = ? AND users.age > ?", "Bob", 50)
The Doctor.joins(:user) portion of the ActiveRecord statement performs an inner join, thereby eliminating all doctors that do not have a user record from further consideration. In the subsequent where() portion of the ActiveRecord statement, any attribute of the doctors or users table can be accessed with dot notation. In the Doctor.joins(:user) portion statement, we needed to use singilar words, but in the where portion of the statement, plural words are required. This is potentially confusing, so just remember to always use plural words that correspond with the database table names for all text in the where method.
The ActiveRecord statement generates the following SQL query:
SELECT "doctors".* FROM "doctors" INNER JOIN "users" ON "users"."id" = "doctors"."user_id" WHERE (doctors.name = 'Bob' AND users.age > 50)
ActiveRecord joins are a nice abstraction, so Rails programmers can spend less time worrying about SQL syntax.