A few tips about includes and joins in rails 3

Working with database helper tables in rails, I often see very suboptimal data loading in controllers (especially for index) that results in poor application performance. Use includes and joins, they are your friends!

class Item
  belongs_to :user
end

class User
  has_many :items

  def permissioned_name
    if can_see?
      name
    else
      "Mister"
    end
  end

  def can_see?
    Time.now == Time.now.midnight # shows real name only on midnight
  end
end

This is usually bad in an index view:

items = Item.all

items.each do |i|
  i.user.name # this will result in a separate query for each user, N queries where N is Item.count
end

As a rule of thumb, joins should be used when we need pure data or data aggregation from another table and includes when you need data + logic.

Good use of include:

items = Item.includes(:users) # Loads all items, loads all users. Big memory and ActiveRecord init cost

items.each do |t|
  i.permissioned_name # this will not result in N queries, since rails will preload users association
end

Suboptimal use of include, better than not using it:

items = Item.includes(:users) # Loads all items, loads all users. Big memory and ActiveRecord init cost

items.each do |i|
  i.user.name # No additional query, but we are just taking one data field from user, why do we need all the heavy AR?
end

Use joins for accessing data:

items = Item.joins(:users).group("item.id").select("items.*, count(users.id) as user_count")
# this will result in query like this:
# SELECT items.*, count(users.id) as user_count FROM `items` INNER JOIN `users` ON `items`.`user_id` = `users`.`id` GROUP BY items.id

items.each do |i|
  i.user_count # notice that I can't use i.users.size, I have to use method user_count (which accesses the attribute loaded from DB)
end

For fun, don't use this ever:

items = Item.includes(:users) # Loads all items, loads all users. Big memory and ActiveRecord init cost

items.each do |i|
  t.users.count # N queries to DB for users count since count method is used!
end