SQL and ActiveRecord compared

Some comparisons of ActiveRecord method calls and the equivalent SQL for associations, starting simple and progressing to joins and chained joins.

User.first
SELECT * FROM "users" LIMIT 1

User.last
SELECT * FROM "users" ORDER BY users.id DESC LIMIT 1

User.find(1)
SELECT * FROM "users" WHERE ("users"."id" = 1)

has_one

@user.address

SELECT * FROM "addresses" WHERE ("addresses".user_id = 999) LIMIT 1

(where 999 is the id of @user)

belongs_to

@address.user

SELECT * FROM "users" WHERE ("users"."id" = 999)

has_many

@user.memberships

SELECT * FROM "memberships" WHERE ("memberships".user_id = 999)

has_many :through

@user.groups

SELECT "groups".* FROM "groups"
INNER JOIN "memberships" ON "groups".id = "memberships".group_id 
WHERE (("memberships".user_id = 999))

has_and_belongs_to_many

@post.groups

SELECT * FROM "groups" 
INNER JOIN "groups_posts" ON "groups".id = "groups_posts".group_id 
WHERE ("groups_posts".post_id = 999 ) 

(where 999 is the id of @post)

Joins

has_many

Membership.all ( :joins => :user, :conditions => { :users => { :id => @user.id } } )

SELECT "memberships".* FROM "memberships" 
INNER JOIN "users" 
ON "users".id = "memberships".user_id WHERE ("users"."id" = 999) 

(which is equivalent to the @user.memberships example, above)

has_many :through

Group.all( :joins => :memberships,
  :conditions => { :memberships => { :user_id => @user.id } } )

SELECT "groups".* FROM "groups" 
INNER JOIN "memberships" ON memberships.group_id = groups.id 
WHERE ("memberships"."user_id" = 999) 

(which is equivalent to the @user.groups example, above)

has_and_belongs_to_many

Group.all(:joins => :posts, :conditions => { :groups_posts => {:post_id => 999 }})

SELECT "groups".* FROM "groups" 
INNER JOIN "groups_posts" ON "groups_posts".group_id = "groups".id 
INNER JOIN "posts" ON "posts".id = "groups_posts".post_id 
WHERE ("groups_posts"."post_id" = 999) 

(which is equivalent to the @post.groups example, above—although it doesn’t return the columns of the join table)

Chained joins

@group.users

User.all(:joins => {:memberships => :group }, :conditions => {
  :group => {:id => 999 } } )

SELECT "users".* FROM "users" 
INNER JOIN "memberships" ON memberships.user_id = users.id 
INNER JOIN "groups" ON "groups".id = "memberships".group_id 
WHERE ("groups"."id" = 999)

Which is just an even more long-winded version of:

User.all(:joins => :memberships, :conditions => {
  memberships => { :group_id => 999 } } )

Note that memberships is plural and group is singular because this is expressing a many-to-one relationship.

A more complicated case

User.all(:joins => {:memberships => { :group => :posts } }, :conditions => { :posts => {:id => 999 } }, :select => “DISTINCT users.*” )

SELECT DISTINCT users.* FROM "users" 
INNER JOIN "memberships" ON memberships.user_id = users.id 
INNER JOIN "groups" ON "groups".id = "memberships".group_id 
INNER JOIN "groups_posts" ON "groups_posts".group_id = "groups".id 
INNER JOIN "posts" ON "posts".id = "groups_posts".post_id 
WHERE ("posts"."id" = 999) 

This selects all the users who are members of groups to which the post with an id of 999 has been posted.

Note the use of the :select option in order to return only distinct users.

And note again the selective pluralization of the hash keys to capture the nature of each association.

See Chaining :include’s in Rails to reduce the number of SQL queries and
What’s New in Edge Rails: Easy Join Table Conditions