What's the proper way to delete all associated children in one SQL statement, with AR 4.0 ?

rubyonrails-talk
1

Hi

Let’s imagine these models, with ActiveRecord 4.0

class Parent < ActiveRecord::Base

has_many :items

end

class Item < ActiveRecord::Base

belongs_to :parent

end

I need to delete all items associated to one parent, without callback.

I don’t need to call #destroy, but only executing the appropriate SQL statement.

First case :

parent.items.delete_all

In Rails 4.1 delete_all on associations would not fire callbacks. It means if the :dependent option is :destroy then the associated records would be deleted without loading and invoking callbacks.

Item Load (0.3ms) SELECT “items”.* FROM “items” WHERE “items”.“parent_id” = ? [[“parent_id”, 1]]

(0.1ms) begin transaction

SQL (1.1ms) DELETE FROM “items” WHERE “items”.“id” = ? [[“id”, 44]]

SQL (0.1ms) DELETE FROM “items” WHERE “items”.“id” = ? [[“id”, 43]]

SQL (0.1ms) DELETE FROM “items” WHERE “items”.“id” = ? [[“id”, 42]]

SQL (0.1ms) DELETE FROM “items” WHERE “items”.“id” = ? [[“id”, 41]]

…

(80.0ms) commit transaction

I got one delete per item. Not perfect !

Now, let’s try a workaround :

parent.items.scoped.delete_all

DEPRECATION WARNING: Model.scoped is deprecated. Please use Model.all instead.

SQL (0.1ms) DELETE FROM “items” WHERE “items”.“parent_id” = ? [[“parent_id”, 1]]

It works fine: only one statement.

But according to the warning, it seems to not be the proper way to do.

So, OK, let’s try :

feed.items.all.delete_all

DEPRECATION WARNING: Relation#all is deprecated. If you want to eager-load a relation, you can call #load (e.g. Post.where(published: true).load).

If you want to get an array of records from a relation, you can call #to_a (e.g. Post.where(published: true).to_a).

Item Load (0.3ms) SELECT “items”.* FROM “items” WHERE “items”.“feed_id” = ? [[“feed_id”, 1]]

NoMethodError: undefined method `delete_all’ for #Array:0x007f87ae6fee00

from ~/.rvm/gems/ruby-2.0.0-p0@global/gems/rush-0.6.8/lib/rush/find_by.rb:16:in `method_missing’

from (irb):24

from ~/.rvm/gems/ruby-2.0.0-p0@global/gems/railties-4.0.0/lib/rails/commands/console.rb:90:in `start’

from ~/.rvm/gems/ruby-2.0.0-p0@global/gems/railties-4.0.0/lib/rails/commands/console.rb:9:in `start’

from ~/.rvm/gems/ruby-2.0.0-p0@global/gems/railties-4.0.0/lib/rails/commands.rb:64:in `<top (required)>’

from bin/rails:4:in `require’

from bin/rails:4:in `’

I also tried to call #load : I got the same result as for the first case (too many DELETE statements).

I can also do that :

Item.where(feed: feed).delete_all

SQL (0.2ms) DELETE FROM “feed_items” WHERE “feed_items”.“feed_id” = 1

Works fine. No warning. But let’s forget the OOP.

So… What’s the proper way ?

2

Savater Sebastien wrote in post #1118021:

class Parent < ActiveRecord::Base   has_many :items end

class Item < ActiveRecord::Base   belongs_to :parent end

I need to delete all items associated to one parent, without callback. I don't need to call #destroy, but only executing the appropriate SQL statement.

First case :

parent.items.delete_all

... So.. What's the proper way ?

If I understand correctly your intent is to delete all "items" associated to "parent" but not delete the "parent" itself. If that assumption is correct you don't have to use the association at all. Just delete what you want directly from the Item model as follows:

Item.delete_all([ "parent_id = ?", parent.id ]) or Item.where([ "parent_id = ?", parent.id ]).delete_all

I would also recommend using the :dependent option on your association. It makes no sense for "items" to exist without their owning "parent" object. In other words, parent "owns" its items.

class Parent < ActiveRecord::Base   has_many :items, :dependent => :destroy end

This way you won't orphan items if a parent were to be destroyed.

3

I knew I can do that (last example, I made a bad copy/paste of arguments :))

Item.where(parent: parent.id).delete_all

But I hoped we can use associations, which offering a better readability.

In Rails 3, you can simply make :

parent.items.delete_all

Otherwise, I already use :dependent option (with :delete_all, in my case).

As, It’s not the point here, I tried to keep my example simple.