ActiveRecord joins in Rails

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s