Is it possible to delete_all with inner join conditions?

Question

I need to delete a lot of records at once and I need to do so based on a condition in another model that is related by a "belongs_to" relationship. I know I can loop through each checking for the condition, but this takes forever with my large record set because for each "belongs_to" it makes a separate query.

Here is an example. I have a "Product" model that "belongs_to" an "Artist" and lets say that artist has a property "is_disabled".

If I want to delete all products that belong to disabled artists, I would like to be able to do something like:

Product.delete_all(:joins => :artist, :conditions => ["artists.is_disabled = ?", true])

Is this possible? I have done this directly in SQL before, but not sure if it is possible to do through rails.

Answer 1

The problem is that delete_all discards all the join information (and rightly so). What you want to do is capture that as an inner select.

If you're using Rails 3 you can create a scope that will give you what you want:

class Product < ActiveRecord::Base
  scope :with_disabled_artist, lambda {
    where("product_id IN (#{select("product_id").joins(:artist).where("artist.is_disabled = TRUE").to_sql})")
  }
end

You query call then becomes

Product.with_disabled_artist.delete_all

You can also use the same query inline but that's not very elegant (or self-documenting):

Product.where("product_id IN (#{Product.select("product_id").joins(:artist).where("artist.is_disabled = TRUE").to_sql})").delete_all

Answer 2

In Rails 4 (I tested on 4.2) you can almost do how OP originally wanted

Application.joins(:vacancy).where(vacancies: {status: 'draft'}).delete_all

will give

DELETE FROM `applications` WHERE `applications`.`id` IN (SELECT id FROM (SELECT `applications`.`id` FROM `applications` INNER JOIN `vacancies` ON `vacancies`.`id` = `applications`.`vacancy_id` WHERE `vacancies`.`status` = 'draft') __active_record_temp)