Deleting records en mass from database

Rails
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.

On Mon, Apr 21, 2008 at 8:02 PM, histrionics [email protected] wrote:

@sitepurchases = SitPurchase.find(:all, :conditions => [ "site_id
purchase.destroy
end
for sitestat in @sitestats
sitestat.destroy
end

–

Jeff W.
[email protected]
http://boowebb.com/

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.

On Mon, Apr 21, 2008 at 8:51 PM, histrionics [email protected] wrote:

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.

–

Jeff W.
[email protected]
http://boowebb.com/

5

I recommend using delete_all:

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

Post.delete_all “site_id = #{site_id}”

6

On Apr 21, 2008, at 11:02 PM, histrionics wrote:

params[:id]])
sitestat.destroy
end

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 B. http://agileconsultingllc.com
[email protected]

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

–
Ryan B.

Feel free to add me to MSN and/or GTalk as this email.