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.

score 4 · Accepted Answer · answered Oct 03 '11 at 19:42

4

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

answered Oct 03 '11 at 19:42

gcastro

6,266
3
18
14

2

How is it "rightly so" to remove the joins? Join conditions are often needed to limit the records to be deleted. I think the worst part is that it does so silently, which could be quite surprising. – Andrew Vit Sep 18 '14 at 21:55
I just mean that you can't do a join delete all like that in SQL anyway, hence the rightly so. I'm pretty sure later versions of Rails do sub-queries anyway.Not sure that's the best approach since it makes it seem the underlaying query in a join when it's really a sub-query. – gcastro Nov 09 '14 at 20:32
1

@gcastro That is untrue -- in mysql at least one can absolutely perform a delete with a join statement. http://dev.mysql.com/doc/refman/5.7/en/delete.html – bluefear Oct 27 '16 at 00:29

score 1 · Answer 2 · answered Jun 18 '15 at 20:26

1

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)

answered Jun 18 '15 at 20:26

Unknown_Guy

948
9
17

While this is fine on PostgreSQL, it is deadly slow on MySQL server. Beware. – lzap Apr 03 '17 at 12:15

score 0 · Answer 3 · answered Oct 16 '12 at 14:34

0

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

answered Oct 16 '12 at 14:34

RonanOD

876
1
9
19

1

This will result in individual deletes which not the same and will not scale. You should be able to use named scopes in Rails 2. – gcastro Nov 09 '14 at 20:36

Is it possible to delete_all with inner join conditions?

3 Answers3