Deleting records en mass from database

rubyonrails-talk
1

There HAS to be an easier way then this, right?....RIGHT?

It just seems to take forever.

    @site = Site.find(params[:id])     @siteproducts = SiteProduct.find(:all, :conditions => [ "site_id = ?", params[:id]])     @sitepurchases = SitPurchase.find(:all, :conditions => [ "site_id = ?", params[:id]])     @sitestats = SiteStat.find(:all, :conditions => [ "site_id = ?", params[:id]])

    @site.destroy

  for product in @siteproducts     product.destroy   end   for purchase in @sitepurchases     purchase.destroy   end   for sitestat in @sitestats     sitestat.destroy   end

2

tables = %w{site_products site_purchases site_stats} tables.each { |table| ActiveRecord::Base.Connection.execute(“truncate #{table}”) }

be warned that truncates don’t show up in db logs.

3

indeed, but truncate would delete all record from the table whereas I just want to selectively delete those rows that have a site_id column matching the id value passed to the function.

4

you could execute the raw sql in that manner by passing in the id to a delete statement then.

def delete_site_stuff(site_id) ActiveRecord::Base.connection.execute(“delete from site_products where site_id = #{site_id}”) end

if you’re assured that site_id cannot be injected that would be fine. Otherwise you should run it through the sanitize_sql method.

5

I recommend using delete_all:

http://api.rubyonrails.com/classes/ActiveRecord/Base.html#M001385

Post.delete_all "site_id = #{site_id}"

6

class Site    has_many :site_products, :dependent => :destroy    has_many :site_purchases, :dependent => :destroy    has_many :site_stats, :dependent => :destroy end

@site.destroy

Which may be just as slow...

Or, if you don't need to actually instantiate and destroy each record (for example, so callbacks will run), then you can replace the associations with :dependent => :delete_all. That should result in SQL like "DELETE FROM site_products WHERE site_id = #{params[:id]}"

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

7

try delete_all - http://api.rubyonrails.com/classes/ActiveRecord/Base.html#M001382 which will delete everything in 1 SQL statement. If you need to worry about dependent associations or callbacks try destroy_all which will do the looping for you (but be just as slow).

8

If these are related to one another you could always use something like:

has_many :blahs, :dependent => :destroy