Thanks John. Upsert looks like a great option. I wish I would have known about it earlier :). When I said UK I just meant that order_id item_id and quantity were the only way I could differentiate unique records from the JSON API because the API side does not have the PK from the DB side. I don't like that it's PostgreSQL specific but the chances of porting are pretty slim...
Mason On Thu, Nov 21, 2019 at 7:51 AM John W Higgins <[email protected]> wrote: > > > 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 > <https://groups.google.com/d/msgid/sequel-talk/CAPhAwGzAE%3D4OHXz9cfdbsHLS7F4hFe%3Dt6t5fT3_qOvUhEtf6Fg%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- 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/CAEWSjwLTob-c0%2BPHHtajXh1UiqtTzJG%3Dhq36rMCNoNnm2-H3xg%40mail.gmail.com.
