Thanks Jeremy. I continued to be impressed with the Sequel library!
For anyone else who wants it... not sure if this has a place in the library
but it is much more straight-forward and faster than iterating through
records on migrations or using complicated insert_conflict logic.
Here is what I have so far... please test yourself as it's not battle
tested!
# Use like this: User.batch_update([[{id: 4}, {full_name: "New Full
Name"}]])
def self.batch_update(data_lookup, return_sql: false)
if data_lookup.present?
lookup_columns, update_columns = data_lookup.first.map(&:keys)
from, to = Sequel[:data], Sequel[table_name]
conditions = lookup_columns.each.with_object({}) { |col, h|
h[to[col]] = from[col] }
updates = update_columns.each.with_object({}) { |col, h| h[col] =
from[col] }
values = data_lookup.map { |l, u| l.values + u.values }
ds = db.
from(
to.value.to_sym,
db.values(values).as(from.value.to_sym, [*lookup_columns,
*update_columns])
).
where(conditions)
ds.send(return_sql ? :update_sql : :update, updates)
end
end
On Wednesday, August 9, 2023 at 4:24:47 PM UTC-7 Jeremy Evans wrote:
> On Wed, Aug 9, 2023 at 11:35 AM [email protected] <[email protected]>
> wrote:
>
>> Hi Jeremy,
>>
>> I saw some historical posts about batch updates like this one:
>>
>> https://groups.google.com/g/sequel-talk/c/uV7PRbEvQj0/m/lCvZaLx8AAAJ
>>
>> It feels a little "hacky" to say insert this record with a unique primary
>> key that already exists...when you get an error, then update it.
>>
>> I've been using a solution like this:
>>
>> run <<-SQL
>> update user_expo_push_tokens as uept set -- postgres FTW
>> device_id = ud2.device_id
>> from (values
>> #{updates.join(", ")}
>> ) as ud2(installation_id, device_id)
>> where ud2.installation_id = uept.installation_id;
>> SQL
>>
>> This also works in the case where the values are not a primary key or
>> have a unique index to throw the "on duplicate key update" logic.
>>
>> Is there a way to accomplish this without having to drop to straight sql?
>>
>
> I think this will work:
>
> DB.from(Sequel.as(:user_expo_push_tokens, :uept),
> DB.values(updates).as(:ud2, [:installation_id, :device_id])).
> where{{ud2[:installation_id]=>uept[:installation_id]}}.
> update(:device_id => Sequel[:ud2][:device_id])
>
> SQL:
>
> UPDATE "user_expo_push_tokens" AS "uept"
> SET "device_id" = "ud2"."device_id"
> FROM (VALUES ...) AS "ud2"("installation_id", "device_id")
> WHERE ("ud2"."installation_id" = "uept"."installation_id")
>
> It depends on what "updates" is supposed to be.
>
> Thanks,
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/1d21ebcf-8ec2-4fb9-b6b7-229c019fe686n%40googlegroups.com.
