是否可以使用内连接条件删除所有？

发布于 2024-10-04 02:09:20 字数 400 浏览 6 评论 0原文

我需要一次删除大量记录，并且需要根据另一个模型中通过“belongs_to”关系相关的条件来执行此操作。我知道我可以循环检查每个条件，但这对于我的大型记录集来说需要很长时间，因为对于每个“belongs_to”，它都会进行单独的查询。

这是一个例子。我有一个“属于”“艺术家”的“产品”模型，可以说艺术家有一个属性“is_disabled”。

如果我想删除属于残疾艺术家的所有产品，我希望能够执行以下操作：

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

这可能吗？我之前已经直接在 SQL 中完成此操作，但不确定是否可以通过 Rails 完成。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

百善笑为先 2024-10-11 02:09:20

问题是delete_all 丢弃所有连接信息（这是正确的）。您想要做的是将其捕获为内部选择。

如果您使用 Rails 3，您可以创建一个范围来提供您想要的内容：

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

您查询调用然后变为

Product.with_disabled_artist.delete_all

您也可以内联使用相同的查询，但这不是很优雅（或自记录）：

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

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

回复收藏 0 原文

赴月观长安 2024-10-11 02:09:20

在Rails 4（我在4.2上测试过）中，你几乎可以做到OP最初想要的

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

方式

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)

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)

回复收藏 0 原文

月牙弯弯 2024-10-11 02:09:20

如果您使用的是 Rails 2，则无法执行上述操作。另一种方法是在 find 方法中使用 joins 子句，并对每个项目调用 delete。

    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

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

回复收藏 0 原文

~没有更多了~