删除使用ActiveRecord的助手VS寻找孤儿 [英] Deleting VS Finding Orphans using ActiveRecord helpers

问题描述

我想删除所有的组织不再有任何用户。

使用跌破code，我能找到我所要删除的记录：

  Organization.includes（：用户）
  。凡（网友：{ID：无}）
  .references（：用户）
 

当我加入 DELETE_ALL ，我得到了同样的错误，我会得到，如果我不包括引用：

  PG :: UndefinedTable：错误：缺少子句条目表用户
 

我大概可以写在纯SQL的解决方案，但我不明白为什么Rails的不保持引用用户当我添加了 DELETE_ALL 语句。

下面是一些更多的细节：

 组织：
  的has_many：用户

用户：
  belongs_to的：组织
 

解决方案

我已经找到了包括仅供预先加载有用的（并且可以很少处理我的案件），并配以引用它生成时的一些完全疯了（走样的东西，每一个领域像 tN_rM ），尽​​管它实际上做了 LEFT OUTER JOIN ...其中的可以的，如果它没帮助牛逼的绝响 DELETE_ALL 出现一次！

我发现，它的更清晰，简单的方法是使用存在。这是阿雷尔（而且也没有一点回避它，它下的ActiveRecord反正罩），但它是这样一个微小的部分，它是几乎没有明显的：

  Organization.where（
  User.where（'users.organization_id = organizations.id'）。exists.not
）
 

或者，如果该字符串的SQL并不好看你，用一点阿雷尔，所以它得到明显的：

  Organization.where（
  User.where（ORGANIZATION_ID：Organization.arel_table [：ID]）。exists.not
）＃我倾向于提取这些​​^^^^^^^^^^^^^^^^^^^^^^^到本地变量
 

这是处理在上面就好了链接 .delete_all ，因为它不是（语法）的连接，即使它实际上等同于一个。

的背后

魔法

SQL有一个 EXISTS 操作符是在功能上加入类似的，只是从连接表中选择字段无力。它形成了一个有效的布尔EX pression可以是否定和扔进，其中 -conditions

在SQL-自由的形式，我使用，这原来是在Rails的哈希条件下获得可用的前pression表列。这是一个意外的发现，阿雷尔的几次使用不会使code过于笨重之一。

I'm trying to delete all the organizations that no longer have any users.

Using the below code, I can find all the records I wish to delete:

Organization.includes(:users)
  .where(users: { id: nil })
  .references(:users)

When I add delete_all, I get the same error I would get if I didn't include references:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "users"

I could probably write the solution in pure SQL, but I don't understand why Rails isn't keeping the reference to users when I add the delete_all statement.

Here are some more details:

Organization:
  has_many :users

User:
  belongs_to :organization

解决方案

I've found the includes useful only for eager loading (and it can rarely handle my cases), and when coupled with references it generates something completely insane (aliasing every single field with something like tN_rM) even though it actually does a LEFT OUTER JOIN... Which could help if it didn't vanish once delete_all appears!

I've found that it's much clearer and simpler just to use exists. It's Arel (and there's no point in avoiding it, its under the hood of ActiveRecord anyway), but it's such a tiny portion that it's barely noticeable:

Organization.where(
  User.where('users.organization_id = organizations.id').exists.not
)

Or, if this string of SQL doesn't look nice to you, use a bit more Arel, so it gets noticeable:

Organization.where(
  User.where(organization_id: Organization.arel_table[:id]).exists.not
) # I tend to extract these   ^^^^^^^^^^^^^^^^^^^^^^^ into local variables

That handles chaining .delete_all on top just fine, since it's not (syntactically) a join, even though it's effectively equivalent to one.

The magic behind this

SQL has an EXISTS operator that is similar in functionality to a join, except for inability of selecting fields from a joined table. It forms a valid boolean expression which can be negated and thrown into WHERE-conditions.

In the "SQL-free" form I'm using an expression "column of a table", which turns out to be usable in Rails' hash-conditions. It's an accidental discovery, one of the few uses of Arel that does not make code too bulky.

这篇关于删除使用ActiveRecord的助手VS寻找孤儿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！