On Friday, February 16, 2018 at 1:54:02 PM UTC-8, Max Farrar wrote:
>
> I'm trying to optimize how many queries my code executes. I want to do an
> insert_conflict.insert, and then use that reference to link it to another
> table row. However, at the moment, I have to do insert_conflict.insert, and
> *then* I have to do a query to find that row so I can use it's reference..
>
> Code example, this is where I'm adding various store prices for an item.
>
> @db[:items].insert_conflict.insert(:sku => ..., ...)
> item_ref = Items.where(:sku => sku).first. # this is the reference I
> have to attain
> price_ref = Price.where(:item_id => item_ref, :store_id => store)
> if price_ref == nil
> ....
> end
>
> Ideally I'd like to do
>
> item_ref = @db[:items].insert_conflict.insert(:sku => ...)
> price_ref = Price.where(:item_id => item_ref, :store_id => store)
> if price_ref == nil
> ....
> end
>
> Is there a better way to handle this?
>
PostgreSQL only returns a value if it inserts or updates the row. You
could probably do the following, but it could be less efficient:
@db[:items].insert_conflict(:target=>:sku,
:update=>{:id=>Sequel[:items][:id]}).insert(:sku=>'...')
This will actually update the table row to the same value it already has,
though. I'm not sure if that is better than a separate query to get the
resulting value.
If you really want to make this more efficient you'd have to ask the
PostgreSQL developers for the ability to return values from the conflicting
rows, or write a database function.
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 post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
