Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ActiveRecord Bulk / Batch Update
# frozen_string_literal: true
module ModelSupportsBulkUpdates
extend ActiveSupport::Concern
module ClassMethods
# Performs a bulk update with an efficient single query for all the records in the list.
# Note that the records are not reloaded form the database.
# This means that ActiveRecord will still see these records as dirty after the bulk_update.
def bulk_update(records)
records = records.reject { |r| r.new_record? || r.destroyed? || !r.changed? }
grouped = records.group_by { |r| [r.class, r.changed] }
grouped.each do |(model_class, changed_column_names), record_list|
next if record_list.empty?
# generates a query that looks like this
#
# UPDATE users AS u
# SET first_name = tmp.first_name, last_name = tmp.last_name
# FROM (VALUES (1, 'Luke', 'Skywalker'), (2, 'Leia', 'Organa'), (2, 'Han', 'Solo')) AS tmp(id, first_name, last_name)
# WHERE u.id = tmp.id
connection = model_class.connection
table_alias = "_#{SecureRandom.alphanumeric}"
temp_table_alias = "_#{SecureRandom.alphanumeric}"
temp_table_columns = [ connection.quote_column_name(:id) ]
changed_column_names.each do |column_name|
temp_table_columns << connection.quote_column_name(column_name)
end
sets = changed_column_names.map do |column_name|
quoted_column_name = connection.quote_column_name(column_name)
"#{quoted_column_name} = #{temp_table_alias}.#{quoted_column_name}"
end
values = record_list.map do |record|
record_values = [cast_and_quoted_value(:id, record.id, model_class)]
changed_column_names.each do |column_name|
record_values << cast_and_quoted_value(column_name, record.send(column_name), model_class)
end
"(#{record_values.join ", "})"
end
id_sql_type = model_class.columns_hash["id"].sql_type
query = <<~QUERY
UPDATE #{model_class.quoted_table_name} AS #{table_alias}
SET #{sets.join ", "}
FROM (VALUES #{values.join ", "}) AS #{temp_table_alias}(#{temp_table_columns.join ", "})
WHERE #{table_alias}.#{connection.quote_column_name :id} = CAST(#{temp_table_alias}.#{connection.quote_column_name :id} AS #{id_sql_type})
QUERY
connection.execute query
end
true
end
private
def cast_and_quoted_value(column_name, value, model_class=nil)
model_class ||= self
connection = model_class.connection
arel_table = model_class.arel_table
cast_value = arel_table[column_name.to_sym].type_cast_for_database(value)
connection.quote_default_expression cast_value, model_class.columns_hash[column_name.to_s]
end
end
included do
delegate :bulk_update, to: "self.class"
end
end
@hopsoft

This comment has been minimized.

Copy link
Owner Author

@hopsoft hopsoft commented Jan 12, 2018

a = User.find(1)
b = User.find(2)
c = User.find(3)

a.first_name = "Luke"
a.last_name = "Skywalker"

b.first_name = "Leia"
b.last_name = "Organa"

c.first_name = "Han"
c.last_name = "Solo"

bulk_update [a, b, c]

#   UPDATE users AS u
#   SET first_name = tmp.first_name, last_name = tmp.last_name
#   FROM (VALUES (1, 'Luke', 'Skywalker'), (2, 'Leia', 'Organa'), (2, 'Han', 'Solo')) AS tmp(id, first_name, last_name)
#   WHERE u.id = tmp.id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.