Support of batched UPSERT and UPDATE queries

[Ladas]

Hello,

I would like to use arel to build these 3 batch queries, I am now building manually for PG 9.5:

1) INSERT ON CONFLICT UPDATE with a where condition (UPSERT)

        values = hashes.map do |hash|
          "(#{all_attribute_keys_array.map { |x| ActiveRecord::Base.connection.quote(hash[x]) }.join(",")})"
        end.join(",")

        insert_query = %{
          INSERT INTO #{table_name} (#{all_attribute_keys_array.join(",")})
            VALUES
              #{values}
          ON CONFLICT (#{inventory_collection.unique_index_columns.join(",")})
            DO
              UPDATE
                SET #{all_attribute_keys_array.map { |x| "#{x} = EXCLUDED.#{x}" }.join(", ")}
              WHERE EXCLUDED.remote_data_timestamp IS NULL OR (EXCLUDED.remote_data_timestamp > #{table_name}.remote_data_timestamp)
        }

2) UPDATE FROM VALUES

        values = hashes.map do |hash|
          "(#{all_attribute_keys_array.map { |x| quote(hash[x], x, inventory_collection) }.join(",")})"
        end.join(",")
        cond = inventory_collection.unique_index_columns.map do |x|
          "updated_values.#{x} = #{table_name}.#{x}"
        end.join(" AND ")

        update_query = %{
          UPDATE #{table_name}
            SET
              #{all_attribute_keys_array.map { |key| "#{key} = updated_values.#{key}" }.join(",")}
          FROM (
            VALUES
              #{values}
          ) AS updated_values (#{all_attribute_keys_array.join(",")})
          WHERE #{cond}
        }

3) SELECT using IN with nested array

  model.where("(a,b,c) IN (('a1', 'b1', 'c1'), ('a2', 'b2', 'c2'), ...)")

Is something like this already supported in Arel, I couldn't find it, would it make sense to add it? There are few gems supporting at least the upsert, https://github.com/zdennis/activerecord-import and https://github.com/jesjos/active_record_upsert. But I don't see any for the other queries. Also we would prefer to use Arel.

Can anybody advise?

[Ladas]

@tenderlove could you advise? Thank you in advance :-)

[matthewd]

Per #523, Arel development is moving to rails/rails.

If this issue is still relevant, please consider reopening it over there. (Note that the Rails repository does not accept feature request issues, and requires reproduction steps that rely on Active Record's documented API.)