ActiveRecord Bulk / Batch Update

model_supports_bulk_updates.rb

# 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

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