Iterate through parameters array and ActiveRecord:Relation

Question

I have a controller which needs to implement bulk update. (However, it needs to set specific values to each object vs the same value for all objects).

Here is the array which the controller will get

[
  {
    "task_id": 1,
    "some_property": "value1"
  },
  {
    "task_id": 2,
    "some_property": "value2"
  },
]

I need to find all tasks and for each task update the property to a provided value.

The obvious solution is

task_ids = params[::_json].map { |task| task[:task_id] }
tasks = Task.where(id: task_ids)

tasks.each do |task| 
  params[::_json].each do |task_from_params| do
    if task.id == task_form_params[:task_id] 
       task.some_property = task_form_params[:some_property]
       task.save!
    end
  end
end

The thing which I don't like (big time) is the code where we do N^2 comparisons.

I am pretty sure there should be a better way to do in Rails. I am looking for something more concise which doesn't require N^2 comparisons.

I like the idea of a temporary table in the database for this. Not sure there's an ORM to accomplish the same. — vol7ron
– vol7ron, Commented Feb 27, 2018 at 0:22
CREATE TEMPORARY TABLE foo (task_id, property, value); … then PUT/COPY statements to load temp table. Then UPDATE tasks SET property = foo.value FROM foo WHERE primary_table.id = foo.task_id AND foo.property = 'specific_property'; Update for each property name. Of course, you could issue the UPDATE dynamically — vol7ron
– vol7ron, Commented Feb 27, 2018 at 0:31
Issuing a query for the number of properties should be quicker than a query for the number of tasks to update. Unless it's one task with many properties. — vol7ron
– vol7ron, Commented Feb 27, 2018 at 0:34

Greg Navis · Accepted Answer · 2018-02-27 00:34:55Z

1

Option 1: `ActiveRecord::Relation#update_all`

If you don't care about validations and callbacks then you can simply use:

params.each do |param|
  Task.where(id: param.fetch('task_id')).
    update_all(some_property: param.fetch('some_property')
end

This will iterate over N items in params and issue N UPDATEs and no SELECTs.

Option 2: Convert to a hash mapping ID to property

If you do care about validations or callbacks then you can convert your input array to a hash first:

# Convert params to a hash. Iterate over N items.
id_to_property = params.map do |param|
  [param.fetch('task_id'), param.fetch('some_property')]
end.to_h

# Load corresponding tasks. Iterate over N keys.
Task.where(id: id_to_property.keys).find_each do |task|
  # Look up the property value - O(1).
  task.update!(some_property: id_to_property[task.id])
end

answered Feb 27, 2018 at 0:34

Greg Navis

2,93412 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Victor Ronin Over a year ago

I like option 2. I think option 1 will end up with N+1 queries problem (semaphoreci.com/blog/2017/08/09/…)

Greg Navis Over a year ago

Are you also updating the associations? If not, then N+1 wont occur. Option 1 will actually issue less queries than option 2 as it won't run any SELECTs.

Victor Ronin Over a year ago

Oh... This is interesting. I was under impression that option 1 will do select first and after it will do update. However, if it doesn't do select then it's actually better

Collectives™ on Stack Overflow

Iterate through parameters array and ActiveRecord:Relation

1 Answer 1

Option 1: `ActiveRecord::Relation#update_all`

Option 2: Convert to a hash mapping ID to property

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option 1: ActiveRecord::Relation#update_all

Option 2: Convert to a hash mapping ID to property

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related

Option 1: `ActiveRecord::Relation#update_all`