ActiveRecord: How to find parents whose ALL children match a condition?

Question

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

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

In pure SQL I could do

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

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

Thanks!

---

I'm using Rails 4.2.1 and PostgreSQL 9.3

Answer 1

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?

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.

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
)

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:

• 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.