Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add ability to provide raw SQL as returning and update to #upsert_all #41933

Merged
merged 3 commits into from
Apr 12, 2021

Conversation

palkan
Copy link
Contributor

@palkan palkan commented Apr 12, 2021

Revamp of #36636.

Summary

Original discussion: #35077 (comment)

  • Added ability to use custom SQL in returning: option:
Article.insert_all(
 [
    { title: "Article 1", slug: "article-1", published: false },
    { title: "Article 2", slug: "article-2", published: false }
  ],
  # Some PostgreSQL magic here to detect which rows have been actually inserted
  returning: Arel.sql("id, (xmax = '0') as inserted, name as new_name")
)
  • Added new update_sql: option to specify SQL fragment to use when updating rows on conflict:
Book.upsert_all(
  [{ id: 1, status: 1 }, { id: 2, status: 1 }],
  on_duplicate: Arel.sql("status = GREATEST(books.status, EXCLUDED.status)")
)

Another example by
@boblail:

ExceptionReport.upsert(new_report, on_duplicatel: Arel.sql("count = count + 1"))

/cc @rafaelfranca

@palkan palkan force-pushed the feat/upsert-all-returning-update-sql branch from 1b262d2 to 3d2c8a5 Compare April 12, 2021 16:05
@palkan palkan force-pushed the feat/upsert-all-returning-update-sql branch from 3d2c8a5 to 8f3c12f Compare April 12, 2021 16:09
```ruby
Book.upsert_all(
[{ id: 1, status: 1 }, { id: 2, status: 1 }],
update_sql: "status = GREATEST(books.status, EXCLUDED.status)"
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Instead of update_sql does it makes sense to use the on_duplicated option? If it is a string we use it as the SQL to update on conflict.

activerecord/CHANGELOG.md Outdated Show resolved Hide resolved
activerecord/CHANGELOG.md Outdated Show resolved Hide resolved
```ruby
Book.upsert_all(
[{ id: 1, status: 1 }, { id: 2, status: 1 }],
update_sql: "status = GREATEST(books.status, EXCLUDED.status)"
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

And to avoid SQL injection we just only accept Arel.sql both here an in the returning option.

@rafaelfranca rafaelfranca merged commit b171b84 into rails:main Apr 12, 2021
@palkan palkan deleted the feat/upsert-all-returning-update-sql branch April 13, 2021 07:03
jorgemanrubia added a commit to basecamp/rails that referenced this pull request Nov 24, 2021
rails#41933 added a new `on_duplicate:` option to `upsert_all`, to allow
providing custom SQL update code. This change makes `on_duplicate` admit
an array of columns too, so that `upsert_all` only updates those
columns when a conflict happens.

This allows limiting the list of updated column in a database-agnostic
way.
jeremy pushed a commit that referenced this pull request Nov 24, 2021
#41933 added a new `on_duplicate:` option to `upsert_all`, to allow
providing custom SQL update code. This change makes `on_duplicate` admit
an array of columns too, so that `upsert_all` only updates those
columns when a conflict happens.

This allows limiting the list of updated column in a database-agnostic
way.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

Successfully merging this pull request may close these issues.

None yet

2 participants