HOME PAGE

Database

• N+1 Problem

One plus N (1+N) Query Problem

Dec 12,2012

See Documentation for Eager Loading solution.

However, there are cases that are hard to Eager Load. One example is:

@leavetype_ids = Leavetype.pluck(:id)

for user in User.all
  for lt in @leavetype_ids
    user.leave_credits.with_lt_id(lt).first_or_initialize
  end
end
scope :with_lt_id, ->(lt) {where(:leavetype_id => lt)}


That's a no-no, a query inside a loop is a heavy procedure. Instead, load them (via includes) and use array methods.

@leavetype_ids = Leavetype.pluck(:id)

for user in User.includes(:leave_credits)
  for lt in @leavetype_ids
    x = first_with_lt_id(lt,user.leave_credits)
    x ||= user.leave_credits.new(:leavetype_id => lt)
  end
end
def first_with_lt_id(lt,coll); coll.select{|c| c.leavetype_id == lt}.first; end


where :select_lt_id is a Array.select method.

EDIT 20121218: Eager loaded the problem but use :select_lt_id still

@users = User.includes({:leave_credits => [:leave_applications, :leavetype]}, :leave_banks, :department)

:include vs :joins

class User; has_many :leave_credits

then User.where(:id => [1,2]).joins(:leave_credits).count

# => 8 # number of leave_credits.
# SELECT COUNT(*) FROM `users` INNER JOIN `leave_credits` ON `leave_credits`.`user_id` = `users`.`id` WHERE `users`.`id` IN (1, 2)
# may lead to 1+n problem

then User.where(:id => [1,2]).includes(:leave_credits).count

# => 2 # number of users.
# SELECT COUNT(*) FROM `users` WHERE `users`.`id` IN (1,2)
# will not query projects until you call user.leave_credits
# avoids 1+n problem

Other infos: Railscast #181

Validation

Triggers validation:

• create
• create!
• save
• save!
• update
• update_attributes
• update_attributes!

Structure

Migration

...migrations are wrapped in a transaction. If the database does not support this (for example MySQL) then when a migration fails the parts of it that succeeded will not be rolled back. You will have to rollback the changes that were made by hand, and rerun the migration.

Active Record supports the following database column types:
• :binary
:boolean
• :date
• :datetime
• :decimal
• :float
• :integer
• :primary_key
• :string
• :text
• :time
• :timestamp

Invertible commands:

• add_column
• add_index
• add_timestamps
• create_table
• remove_timestamps
• rename_column
• rename_index
• rename_table