Select objects where all children have no grandchildren

Question

I have the following three-level hierarchy of models:

class Parent < AR::Base
  has_many :children
end

class Child < AR::Base
  has_many :grandchildren
  belongs_to :parent

  attr_accessible :my_number
end

class Grandchild < AR::Base
  belongs_to :child
end

It is expected that all Parents will have multiple children, but each Child may or may not have any grandchildren.

I want to retrieve all Parent objects for which all the children have no grandchildren. How can I do this? I'd prefer a Rails-way, and I have squeel available; but I'll settle for raw SQL.

Bonus points if you can give me all Parent objects for which all the children have no grandchildren and all the children have my_number < 5.

I note that, of course, `Parent.all.select{|p| p.children.all? {|c| c.grandchildren.empty? && c.my_number < 5}}` does the job. It's not exactly efficient, though. — Chowlett, Feb 18 '15 at 14:07

score 1 · Accepted Answer · edited May 23 '17 at 12:28

After a bit of researching I think I got the solution by using group and having clauses.

You can add this scope to your Parent model. Notice we need to manually define the joins in order to use LEFT JOIN instead of the default INNER JOIN Rails generates. More info on SQL JOINS here.

class Parent < AR::Base
  scope :with_children_having_no_grand_children, -> { 
    joins("LEFT JOIN `children` ON `children`.`parent_id` = `parents`.`id` 
           LEFT JOIN `grand_children` ON `grand_children`.`child_id` = `children`.`id`")
    .group('parents.id')
    .having('COUNT(grand_children.id) = 0 AND COUNT(children.id) > 0')
  }
end

And then you can use:

Parent.with_children_having_no_grand_children

For the second question, assuming :my_number is an attribute stored in DB, you can do:

Parent.with_children_having_no_grand_children.where(children: { my_number: 5 }))

Select objects where all children have no grandchildren

1 Answers1