CODEWITHSUNDEEP
ruby-on-rails-3activerecord
Bradley
Bradley

Reputation: 1724

Active Record: delete_all with limit

Trying to get a definitive answer on whether it's possible to limit a delete_all to X number of records.

I'm trying the following:

Model.where(:account_id => account).order(:id).limit(1000).delete_all

but it doesn't seem to respect the limit and instead just deletes all Model where :account_id => account.

I would expect it to generate the following:

delete from model where account_id = ? order by id limit 1000

This seems to work fine when using destroy_all but I want to delete in bulk.

Upvotes: 6

Views: 6848

Answers (5)

Tom Rossi
Tom Rossi

Reputation: 12066

This was the best solution I used to delete millions of rows:

sql = %{ DELETE FROM model WHERE where_clause LIMIT 1000 }

results = 1
while results > 0 do
  results = ActiveRecord::Base.connection.exec_delete(sql)
end

This performed much faster than deleting in batches where the IDs were being used in the SQL.

Upvotes: 2

Alex Peattie
Alex Peattie

Reputation: 27647

Try:

Model.delete(Model.where(:account_id => account).order(:id).limit(1000).pluck(:id))

Upvotes: 5

leandroico
leandroico

Reputation: 1227

This one also worked pretty well to me (and my needs)

Model.connection.exec_delete('DELETE FROM models ORDER BY id LIMIT 10000', 'DELETE', [])

I know it might seem a bit cumbersome but it'll return the affected rows AND also will log the query through the rails logger. ;)

Upvotes: 4

Bradley
Bradley

Reputation: 1724

ActiveRecord::Base.connection.send(:delete_sql,'delete from table where account_id = <account_id> limit 1000')

You have to use send because 'delete_sql' is protected, but this works.

I found that removing the 'order by' significantly sped it up too.

I do think it's weird that using .limit works with destroy_all but not delete_all

Upvotes: 0

Tiago
Tiago

Reputation: 1337

Or Model.where(:account_id => account).order(:id).limit(1000).map(&:delete), although it is not the best approach if you have thousands of records to delete/destroy.

Model.delete_all() seems to be the best option as it delegates to SQL the task of selecting the records and mass delete them.

Upvotes: -2

Related Questions