scenario

Our service used a table to record each page's annotation place on the PDF. 
We received a feature request that change the PDF page sequence, e.g. user swap the page 2 and page 3, so that application need to：
1. update the page 2’s 10 annotations to page 3
2. update the page 3’s 15 annotations to page 2.

In the ActiveRecord Model, we could think DB schema like below (Use PostgreSQL):

Then, we have the mission:

1. 10 annotation records need updated page attribute from 2 to 3.

2. another 15 annotation records need updated page attribute from 3 to 2.

My team member do the first try and then told me can not work.

# from_page = 2
# to_page = 3

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
end

class File < ApplicationRecord
  has_many :annotations
end

class Annotation < ApplicationRecord
  belongs_to: file
  
  class << self
    
    def swap_annot_page(file_id, from_page, to_page)
      ActiveRecord::Base.transaction do
        file_annots = Annotation.where(file_id: file_id)

        # find page 2's annots
        from_annot = file_annots.where(page: from_page)
        # find page 3's annots
        to_annot = file_annots.where(page: to_page)

        from_annot.update_all(page: to_page)
        to_annot.update_all(page: from_page)
      end
      file_annots.reload
    end

  end
  
end

First TryThe result would be all updated from_annot and to_annot records to page 2.

That's because although we cache the from_annot and to_annot in the parameters when calling RoR update / update_all methods, it will select again the database by using the condition then update the target records.

In line 27, will overwrite line 26's update. Although using the transaction, it still does not work.

So, how can we do? I use another way to fix this situation but hopefully not to look stupid. Here is my code:

My try

# from_page = 2
# to_page = 3

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
end

class File < ApplicationRecord
  has_many :annotations
end

class Annotation < ApplicationRecord
  belongs_to: file
  
  class << self
    
    def swap_annot_page(file_id, from_page, to_page)
      ActiveRecord::Base.transaction do
        file_annots = Annotation.where(file_id: file_id)

        # find and update page 2's annots cache in application memory
        from_annot = file_annots.where(page: from_page).map { |annot| annot.assign_swap_page(to_page) }
        # find and update page 3's annots cache in application memory
        to_annot = file_annots.where(page: to_page).map { |annot| annot.assign_swap_page(from_page) }

        from_annot.map(&:save!)
        to_annot.map(&:save!)
      end
      file_annots.reload
    end

  end
  
  # You can use dynamic change by using `send` method,
  # or send `key's array` for input to assign to each column value.
  # or use each loop at swap_annot_page method to assign values
  def assign_swap_page(value)
    self.page = value
  end
  
end

The tip is to set the value in parameters and then call the save! method at last.

It will temp store the objects in your memory cache and not select the database again. In other words, your change would not be overwritten.

This is the truly happened in my team member's issue. At that time I already understood that update_all is working for this processing.

Hope the article would help when you faced the same problem. =)

scenario

Our service used a table to record each page's annotation place on the PDF. 
We received a feature request that change the PDF page sequence, e.g. user swap the page 2 and page 3, so that application need to：
1. update the page 2’s 10 annotations to page 3
2. update the page 3’s 15 annotations to page 2.

In the ActiveRecord Model, we could think DB schema like below (Use PostgreSQL):

Then, we have the mission:

1. 10 annotation records need updated page attribute from 2 to 3.

2. another 15 annotation records need updated page attribute from 3 to 2.

My team member do the first try and then told me can not work.

# from_page = 2
# to_page = 3

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
end

class File < ApplicationRecord
  has_many :annotations
end

class Annotation < ApplicationRecord
  belongs_to: file
  
  class << self
    
    def swap_annot_page(file_id, from_page, to_page)
      ActiveRecord::Base.transaction do
        file_annots = Annotation.where(file_id: file_id)

        # find page 2's annots
        from_annot = file_annots.where(page: from_page)
        # find page 3's annots
        to_annot = file_annots.where(page: to_page)

        from_annot.update_all(page: to_page)
        to_annot.update_all(page: from_page)
      end
      file_annots.reload
    end

  end
  
end

First TryThe result would be all updated from_annot and to_annot records to page 2.

That's because although we cache the from_annot and to_annot in the parameters when calling RoR update / update_all methods, it will select again the database by using the condition then update the target records.

In line 27, will overwrite line 26's update. Although using the transaction, it still does not work.

So, how can we do? I use another way to fix this situation but hopefully not to look stupid. Here is my code:

My try

# from_page = 2
# to_page = 3

class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true
end

class File < ApplicationRecord
  has_many :annotations
end

class Annotation < ApplicationRecord
  belongs_to: file
  
  class << self
    
    def swap_annot_page(file_id, from_page, to_page)
      ActiveRecord::Base.transaction do
        file_annots = Annotation.where(file_id: file_id)

        # find and update page 2's annots cache in application memory
        from_annot = file_annots.where(page: from_page).map { |annot| annot.assign_swap_page(to_page) }
        # find and update page 3's annots cache in application memory
        to_annot = file_annots.where(page: to_page).map { |annot| annot.assign_swap_page(from_page) }

        from_annot.map(&:save!)
        to_annot.map(&:save!)
      end
      file_annots.reload
    end

  end
  
  # You can use dynamic change by using `send` method,
  # or send `key's array` for input to assign to each column value.
  # or use each loop at swap_annot_page method to assign values
  def assign_swap_page(value)
    self.page = value
  end
  
end

The tip is to set the value in parameters and then call the save! method at last.

It will temp store the objects in your memory cache and not select the database again. In other words, your change would not be overwritten.

This is the truly happened in my team member's issue. At that time I already understood that update_all is working for this processing.

Hope the article would help when you faced the same problem. =)