diff options
Diffstat (limited to 'doc/development/database/setting_multiple_values.md')
| -rw-r--r-- | doc/development/database/setting_multiple_values.md | 59 |
1 files changed, 28 insertions, 31 deletions
diff --git a/doc/development/database/setting_multiple_values.md b/doc/development/database/setting_multiple_values.md index 54870380047..0f23aae9f79 100644 --- a/doc/development/database/setting_multiple_values.md +++ b/doc/development/database/setting_multiple_values.md @@ -4,24 +4,22 @@ group: Database info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#assignments --- -# Setting Multiple Values +# Update multiple database objects > [Introduced](https://gitlab.com/gitlab-org/gitlab/-/issues/32921) in GitLab 13.5. -There's often a need to update multiple objects with new values for one -or more columns. One method of doing this is using `Relation#update_all`: +You can update multiple database objects with new values for one or more columns. +One method is to use `Relation#update_all`: ```ruby user.issues.open.update_all(due_date: 7.days.from_now) # (1) user.issues.update_all('relative_position = relative_position + 1') # (2) ``` -But what do you do if you cannot express the update as either a static value (1) -or as a calculation (2)? - -Thankfully we can use `UPDATE FROM` to express the need to update multiple rows -with distinct values in a single query. One can either use a temporary table, or -a Common Table Expression (CTE), and then use that as the source of the updates: +If you cannot express the update as either a static value (1) or as a calculation (2), +use `UPDATE FROM` to express the need to update multiple rows with distinct values +in a single query. Create a temporary table, or a Common Table Expression (CTE), +and use it as the source of the updates: ```sql with updates(obj_id, new_title, new_weight) as ( @@ -34,23 +32,22 @@ update issues where id = obj_id ``` -The bad news: there is no way to express this in ActiveRecord or even dropping -down to ARel. The `UpdateManager` does not support `update from`, so this -is not expressible. - -The good news: we supply an abstraction to help you generate these kinds of -updates, called `Gitlab::Database::BulkUpdate`. This constructs queries such as the -above, and uses binding parameters to avoid SQL injection. +You can't express this in ActiveRecord, or by dropping down to [Arel](https://api.rubyonrails.org/v6.1.0/classes/Arel.html), +because the `UpdateManager` does not support `update from`. However, we supply +an abstraction to help you generate these kinds of updates: `Gitlab::Database::BulkUpdate`. +This abstraction constructs queries like the previous example, and uses +binding parameters to avoid SQL injection. ## Usage -To use this, we need: +To use `Gitlab::Database::BulkUpdate`, we need: -- the list of columns to update -- a mapping from object/ID to the new values to set for that object -- a way to determine the table for each object +- The list of columns to update. +- A mapping from the object (or ID) to the new values to set for that object. +- A way to determine the table for each object. -For example, we can express the query above as: +For example, we can express the example query in a way that determines the +table by calling `object.class.table_name`: ```ruby issue_a = Issue.find(..) @@ -63,10 +60,7 @@ issue_b = Issue.find(..) }) ``` -Here the table can be determined automatically, from calling -`object.class.table_name`, so we don't need to provide anything. - -We can even pass heterogeneous sets of objects, if the updates all make sense +You can even pass heterogeneous sets of objects, if the updates all make sense for them: ```ruby @@ -82,8 +76,8 @@ merge_request = MergeRequest.find(..) }) ``` -If your objects do not return the correct model class (perhaps because they are -part of a union), then we need to specify this explicitly in a block: +If your objects do not return the correct model class, such as if they are part +of a union, then specify the model class explicitly in a block: ```ruby bazzes = params @@ -103,7 +97,10 @@ end ## Caveats -Note that this is a **very low level** tool, and operates on the raw column -values. Enumerations and state fields must be translated into their underlying -representations, for example, and nested associations are not supported. No -validations or hooks are called. +This tool is **very low level**, and operates directly on the raw column +values. You should consider these issues if you implement it: + +- Enumerations and state fields must be translated into their underlying + representations. +- Nested associations are not supported. +- No validations or hooks are called. |