On Wed, Nov 20, 2019 at 9:27 PM Mason Kimble <[email protected]> wrote:
> John,
>
> That's an interesting thought. A bit more details below. Would
> appreciate if you have more insight:
>
> I'm basically building an analytics database using a JSON API on one side
> and Postgresql on the other. So on one side I'm getting order_id, item_id,
> and quantity, and other fields via JSON API (no primary key, unique key is
> order_id, item_id, quantity). I am trying to see if they exist in my
> database. If yes, update the record, if not, create them. So if I go with
> your idea, I could conceptually do something like this:
>
>
You are wayyyy over thinking this one - there are much better options here
for you - let the database do what the database does.
First, I hope you mistyped when you said the UK included quantity -
otherwise this makes no sense so I'll assume the typo.
PostgreSQL [1] and Sequel [2] offer methods to let the database handle this
for you. You are trying to do what is generally called an Upsert.
>From [2] you are looking for the 4th example so your code would look
something like
OrderItem.dataset.insert_conflict(target: [:order_id, :item_id], update:
{quantity: Sequel[:excluded][:quantity]}).insert(order_id: x, item_id: y,
quanity: z)
So you are doing three things here
target - listing the unique columns to validate the insert against
update - telling the query what to do with the conflict - in this case -
update the quantity field with the value from the row that failed
insert - standard insert data to attempt here (obviously x, y, and z are
changed to your values)
No need to go back and forth here - PostgreSQL will get this done for you
effectively in one query.
John
[1] - https://www.postgresql.org/docs/current/sql-insert.html
[2] -
https://www.rubydoc.info/github/jeremyevans/sequel/Sequel/Postgres/DatasetMethods#insert_conflict-instance_method
--
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/CAPhAwGzAE%3D4OHXz9cfdbsHLS7F4hFe%3Dt6t5fT3_qOvUhEtf6Fg%40mail.gmail.com.
