CODEWITHSUNDEEP
ruby-on-railsrails-activerecordcascade
Lull
Lull

Reputation: 395

Is it possible to cascade active record deletion in batches?

I have a lot of data I want to delete, and for this reason I use delete/delete_all instead of destroy, via cascade on the foreign keys (on_delete: :cascade).

I want to delete one parent active record which has a couple of "child tables" with many rows. Some of these child tables also have a couple of child tables. For this reason I have added cascade on the foreign keys, such that I only need to call parent.delete to trigger the deletion of all children and grand children of parent.

I want to combine delete/delete_all with active record batches https://api.rubyonrails.org/classes/ActiveRecord/Batches.html, but as there is only one single parent, I am not sure how to combine batches and cascade delete in a neat way.

One option would be to explicitly batch delete the children and grand children, e.g.

parent.children_x.find_each do |child_x|
  child_x.grand_children_y.in_batches do |grand_child_y_batch|
    grand_child_y_batch.delete_all        
  end
  child_x.delete        
end
parent.children_z.in_batches do |child_batch|
  child_batch.delete_all
end
...etc...

but if there is a more implicit way that allows me to only call delete on the parent and batch delete the children and grand children, that would be preferrable, e.g.

parent.cascade_in_batches do |parent_batch|
  parent_batch.delete_all #This batch deletes all children and grand children
end

I see that there is no in_batches on parent as the parent is only a single entity, so it looks like it is only possible if I explicitly delete in batches as in the first example above?

Thanks,

-Louise

Upvotes: 1

Views: 603

Answers (1)

max
max

Reputation: 102036

You really just need to setup the foreign keys to cascade and Postgres will take care of deleting all the way down the line. Since this is implemented on the database layer it does not matter how you trigger the delete from Rails.

class CreateCountries < ActiveRecord::Migration[6.0]
  def change
    create_table :countries do |t|
      t.string :name
      t.timestamps
    end
  end
end

class CreateStates < ActiveRecord::Migration[6.0]
  def change
    create_table :states do |t|
      t.string :name
      t.belongs_to :country, null: false, foreign_key: {on_delete: :cascade}
      t.timestamps
    end
  end
end

class CreateCities < ActiveRecord::Migration[6.0]
  def change
    create_table :cities do |t|
      t.string :name
      t.belongs_to :state, null: false, foreign_key: {on_delete: :cascade}
      t.timestamps
    end
  end
end

Models:

class Country < ApplicationRecord
  has_many :states
  has_many :cities, through: :states
end

class State < ApplicationRecord
  belongs_to :country
  has_many :cities
end

class City < ApplicationRecord
  belongs_to :state
  has_one :country, through: :state
end

Passing spec:

require 'rails_helper'

RSpec.describe Country, type: :model do
  describe "cascading delete" do
    let!(:country){ Country.create }
    let!(:state){ country.states.create }
    let!(:city){ state.cities.create }

    it "deletes the states" do
      expect {
        country.delete
      }.to change(State, :count).from(1).to(0)
    end

    it "deletes the cities" do
      expect {
        Country.delete_all
      }.to change(City, :count).from(1).to(0)
    end
  end
end

If you are using .each_with_batches or not here is kind of irrelevant here. Anything that creates a DELETE FROM countries query is going to fire that database trigger. Unless you really need to evaluate if each parent should be deleted in Rails you should just be able to do:

Country.where(evil: true).delete_all

This is going to be far more efficient then .find_each as you're just doing one SQL query. If you iterate through the records you're doing one DELETE FROM coutries WHERE id = ? query per row and since its blocking Rails has to wait for the round trip to the db.

Upvotes: 2

Related Questions