ActiveRecord的：如何找到父母的儿童ALL符合条件？ [英] ActiveRecord: How to find parents whose ALL children match a condition?

问题描述

假设我有一个父模式，有许多孩子，而孩子也属于 OtherParent 。

Suppose I have a Parent model that has many Child, and that Child also belongs to OtherParent.

我怎样才能找到所有父，所有的儿童属于任何 OtherParent ？

How can i find all Parent where ALL of its Child belongs to any OtherParent?

在纯SQL我可以做

Parent.find_by_sql(<<SQL)
  SELECT *
  FROM parents p
  WHERE NOT EXISTS (
    SELECT *
    FROM children
    WHERE parent_id = p.id
      AND other_parent_id IS NULL
  )
SQL

（从<一个href="http://stackoverflow.com/questions/6430761/rails-how-to-query-for-all-the-objects-whose-every-association-have-an-attribut">here),但我preFER采取的ActiveRecord的优势，如果能够做到这一点。

(from here), but I'd prefer to do it by taking advantage of ActiveRecord if possible.

谢谢！

我用Rails 4.2.1和PostgreSQL 9.3

I'm using Rails 4.2.1 and PostgreSQL 9.3

推荐答案

使用 AREL 可远让你pretty的。最棘手的部分是你如何不使用 AREL 自己的查询语法编写整个查询？

Using arel can get you pretty far. The tricky part is how do you not write your entire query using arel's own query syntax?

这里有一个窍门：使用构建查询时其中，，如果你使用 AREL 的条件下，你会得到一些额外的方法是免费的。例如，您可以尾巴子查询你有没有用 .exists.not ，这将让您有一个（NOT（EXISTS（子查询））） 折腾到这一点父母的其中， -clause和你设置。

Here's a trick: when building your query using where, if you use arel conditions, you get some extra methods for free. For instance, you can tail the subquery you have there with .exists.not, which will get you a (NOT ( EXISTS (subquery))) Toss that into parent's where-clause and you're set.

现在的问题是，你如何引用涉及的表？你需要阿雷尔为。您可以使用阿雷尔的其中，，其丑陋的条件，比如 a.eq b 。但为什么？因为它是一个平等的条件下，你可以使用Rails的条件，而不是！您可以参考你quering用哈希键的表，但对于其他的表（在外部查询中），你可以使用它的 arel_table 。关注此：

The question is, how do you reference the tables involved? You need Arel for that. You could use Arel's where with its ugly conditions like a.eq b. But why? Since it's an equality condition, you can use Rails' conditions instead! You can reference the table you're quering with a hash key, but for the other table (in the outer query) you can use its arel_table. Watch this:

parents = Parent.arel_table
Parent.where(
  Child.where(other_parent_id: nil, parent_id: parents[:id]).exists.not
)

您甚至可以诉诸弦乐器一点，依靠事实，你可以在子查询作为参数馈送到Rails的其中，减少阿雷尔的使用。没有太多的使用它，但它不会强迫你去挖掘到阿雷尔的方法太多了，所以你可以用那招或其他SQL操作，它接受一个子查询（在那里甚至没有任何其他人？）：

You can even reduce Arel usage by resorting to strings a little and relying on the fact that you can feed in subqueries as parameters to Rails' where. There is not much use to it, but it doesn't force you to dig into Arel's methods too much, so you can use that trick or other SQL operators that take a subquery (are there even any others?):

parents = Parent.arel_table
Parent.where('NOT EXISTS (?)',
  Child.where(parent_id: parents[:id], other_parent_id: nil)
)

这里的两个要点是：

The two main points here are:

• 您可以创建子查询只是你用来建立定期查询，引用外部查询的表阿雷尔以同样的方式。它甚至可能不是一个真正的表，它可能是一个别名！疯狂的东西。
• 您可以使用子查询作为参数Rails的其中，方法就好了。

• You can build subqueries just the same way you are used to building regular queries, referencing the outer query's table with Arel. It may not even be a real table, it may be an alias! Crazy stuff.
• You can use subqueries as parameters for Rails' where method just fine.

这篇关于ActiveRecord的：如何找到父母的儿童ALL符合条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！