Searching for a Rails 'delete_by_sql' method

Within ActiveRecord model objects, two methods are provided to allow arbitrary execution of SQL. These are find_by_sql() and count_by_sql(). These are fine for when you’re attempting some particularly tricky SQL SELECT or SELECT COUNT statements, but what happens when you have a tricky DELETE statement you’d also like to craft by hand? There’s no corresponding delete_by_sql()

It turns out you cannot use the aforementioned find_by_sql or count_by_sql to execute a DELETE statement, as they expect the returned data set to have columns defined, and will barf if you don’t have a SELECT. Matt Biddulph pointed me in the right direction, which was to use connection.execute(), or in this case, connection.delete(), from within the model object. These methods are a part of ActiveRecord::Base, so you don’t need to specify the full receiver if you’re already within a class inheriting from it.

In my current rails project, I have a deeply nested set of tables with one set to acts_as_tree, so deleting the top level object, and letting rails propagate the dependent deletions via object instantiation and a call to delete, would take hours. Unfortunately, if you have multiple levels of dependency, you cannot take advantage of the much quicker :dependent => :delete_all, as this will not propagate down your chain of dependent tables.

Instead, the approach I took was to implement a delete_contents() method in the top tier object, and have that delete everything in the dependent tables, without the need for instantiation. Imagine you have the following dependency hierarchy:

Top tier -> Tier 1 -> Tier 2 -> Tier 3

You can implement the following method to achieve a single-statement deletion of all dependent contents:

class TopTier < ActiveRecord::Base
  def delete_contents
    connection.delete("DELETE tier1, tier2, tier3
                       FROM tier1
                       LEFT JOIN tier2 ON (tier2.tier1_id = tier1.id)
                       LEFT JOIN tier3 ON (tier3.tier2_id = tier2.id)
                       WHERE tier1.top_tier_id = #{self.id}")
  end
end

connection.delete() will return the number of rows affected. It’s also worth looking at foreign key constraints in MySQL. This removes much of the pain of maintaining referential integrity yourself, but is not supported by all database systems and table type combinations.