Is it possible to delete_all with inner join conditions?

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 rail开发者_如何学运维s.

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

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)

If you are using Rails 2 you can't do the above. An alternative is to use a joins clause in a find method and call delete on each item.

    TellerLocationWidget.find(:all, :joins => [:widget, :teller_location],
      :conditions => {:widgets => {:alt_id => params['alt_id']},
      :retailer_locations => {:id => @teller_location.id}}).each do |loc|
        loc.delete
    end