ruby on rails - ActiveRecord: How to find parents whose ALL children match a condition? -

suppose have parent model has many child, , child belongs otherparent.

how can find parent of child belongs otherparent?

in pure sql

parent.find_by_sql(<<sql)   select *   parents p   not exists (     select *     children     parent_id = p.id       , other_parent_id null   ) sql 

(from here), i'd prefer taking advantage of activerecord if possible.

thanks!

---

i'm using rails 4.2.1 , postgresql 9.3

using arel can pretty far. tricky part how not write entire query using arel's own query syntax?

here's trick: when building query using where, if use arel conditions, methods free. instance, can tail subquery have there .exists.not, (not ( exists (subquery))) toss parent's where-clause , you're set.

the question is, how reference tables involved? need arel that. could use arel's where ugly conditions a.eq b. why? since it's equality condition, can use rails' conditions instead! can reference table you're quering hash key, other table (in outer query) can use arel_table. watch this:

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

you can reduce arel usage resorting strings little , relying on fact can feed in subqueries parameters rails' where. there not use it, doesn't force dig arel's methods much, can use trick or other sql operators take subquery (are there others?):

parents = parent.arel_table parent.where('not exists (?)',   child.where(parent_id: parents[:id], other_parent_id: nil) ) 

the 2 main points here are:

• you can build subqueries same way used building regular queries, referencing outer query's table arel. may not real table, may alias! crazy stuff.
• you can use subqueries parameters rails' where method fine.