Geek Answers Handbook

ActiveRecord: How to find parents whose ALL children are eligible?

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 his 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 would prefer to do this using ActiveRecord, if possible.

Thanks!

I am using Rails 4.2.1 and PostgreSQL 9.3

+5
source share
3 answers

Using arel can lead you to pretty far. The hard part is, how do you not write your entire query using arel own query syntax?

Here's the trick: when creating a query using where , if you use arel conditions, you get free additional methods. For example, you can .exists.not subquery that you have there with .exists.not , which will give you (NOT ( EXISTS (subquery))) Toss, which is in parent where -clause, and you will set.

The question is, how do you reference linked tables? For this you need Arel. You could use Arel where with its ugly conditions like a.eq b . But why? Since this is an equality condition, you can use Rails conditions instead! You can reference the table you are querying with a hash key, but for another table (in an external query) you can use its arel_table . See this:

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

You can even reduce the use of Arel by resorting to strings a bit and relying on the fact that you can submit subqueries as parameters of Rails' where . It is not so much, but it does not make you delve into Arel methods too often, so you can use this trick or other SQL statements that accept a subquery (are there even others?):

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

Here are two key points:

  • You can create subqueries just like you are used to building regular queries by referencing an external query table using Arel. Perhaps this is not even a real table, it could be an alias! Crazy stuff.
  • You can use subqueries as parameters for the Rails' where method is just fine.
+3
source

Using an exclusive scuttle , you can translate arbitrary SQL into ruby ​​(ActiveRecord and ARel queries)

From this tool, your request is converted to

 Parent.select(Arel.star).where( Child.select(Arel.star).where( Child.arel_table[:parent_id].eq(Parent.arel_table[:id]).and(Child.arel_table[:other_parent_id].eq(nil)) ).ast )

Request split

  • Parent.select(Arel.star) will query all columns in the parent table.
  • Child.arel_table takes you to the world of Arel, allowing you a little more energy to generate your request from ruby. In particular, Child.arel_table[:parent_id] gives you an Arel :: Attributes :: Attribute attribute descriptor, which you can continue to use when building your query.
  • The .eq and .and methods do exactly what you expect, allowing you to create a query of arbitrary depth and complexity.

Not necessarily "cleaner", but completely inside the ruby, which is nice.

+2
source

Given Parent and Child , and the child is in the belongs_to relationship with OtherParent (assuming Rails is used by default):

Parent.joins(:childs).where('other_parent_id = ?', other_parent_id)

-1
source

More articles:


All Articles