jesjos / jesjos/active_record_upsert

Empty record when conditional finds none

Open
#128 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Ruby
Stars
207
Forks
49
PR merge metrics
No merged PRs in 30d

Description

**Context**: The problem is basically https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql/37543015

**Problem**: If you have a conditional upsert, `record.upsert!({arel_condition: upsert_condition})`, and the condition returns no rows, then the `record` object will be empty.

**My situation**: In my case, my condition is about timestamps. ` self.class.arel_table[:shopify_updated_at].lteq(send(:shopify_updated_at))` where Shopify is an external source of data for my app. I only want to upsert if the current record is newer (has a later updated_at).

**Proposed solution**: There are solutions listed in the Stack Overflow discussion. I might suggest a simple solution which is to follow up the INSERT with a subsequent read when nothing is returned. The read should also use the `upsert_keys` if given. This is necessary because otherwise it is not know how to find a matching record.

For me, the `upsert_keys` is usually an external ID column. Something like `shopify_order_id`. I don't want duplicate orders, so I upsert on conflict of duplicate `shopify_order_id`. If a conflict is found, and the `shopify_updated_at` of what I'm trying to upsert is older, then by the way Postgres works, no row will be returned! So the only thing to do is to either remove the condition, modify the INSERT query, or fetch again when empty.

For example purposes only:

```ruby
def _upsert_record(upsert_attribute_names = changed, arel_condition = nil, opts = {})
existing_attribute_names = attributes_for_create(attributes.keys)
existing_attributes = attributes_with_values(existing_attribute_names)
values = self.class._upsert_record(existing_attributes, upsert_attribute_names, [arel_condition].compact, opts)

values = if values.first.to_h.empty? && opts[:upsert_keys].present?
find_by_conditions = opts[:upsert_keys].each_with_object({}) do |upsert_key, unique_attributes|
unique_attributes[upsert_key] = send(upsert_key)
end
existing_record = self.class.find_by(find_by_conditions)
existing_record.attributes
else
values.first.to_h
end

@attributes = self.class.attributes_builder.build_from_database(values)
@new_record = false
changes_applied
values
end
```

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with the _upsert_record method shown in the issue and trace how conditional PostgreSQL upserts populate values when no row is returned. Review the upsert_keys path and verify that a matching existing record is loaded when the condition prevents an insert, while preserving the record attributes and state.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, rails, ruby
Domain
backend, database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.