2

I have the following models:

class GrandParent < ActiveRecord::Base
  has_many :parents
  has_many :children, through: :parents
end

class Parent < ActiveRecord::Base
  has_many :children
  belongs_to :grand_parent
end

class Child < ActiveRecord::Base
  belongs_to :parent
end

I'd like to find all Children where the a child's grand_parent has a value equal to TRUE, but I'm having trouble getting the syntax right. Something like:

Child.where(grand_parent.value: TRUE)
Bailey Smith
  • 2,853
  • 4
  • 27
  • 39

1 Answers1

4

You need to join the models in-between to be able to reference GrandParent so you would have to join Parent first and then filter.

Child.joins(parent: [:grand_parent]).where('grand_parents.value is TRUE')

Just to verify though, is value an actual column on the grand_parents table or do you just want to get all the children that have associated grand_parents?

if so...

Child.joins(parent: [:grand_parent]) should work

if you want to get all the children without associated grand_parent objects you can do

Child.joins(:parent).where('not exists(select 1 from grand_parents where grand_parents.id = parents.grand_parent_id')

it would be slightly different if there's a join table in between like a grand_parent_parents table

Child.joins(:parent).where('not exists(select 1 from grand_parent_parents where grand_parent_parents.parent_id = parent.id')

bf34
  • 284
  • 1
  • 7
  • Yes, the "value" is an actual column on the grand_parents table. – Bailey Smith Mar 10 '16 at 03:34
  • I get an error for this: ActiveRecord::ConfigurationError: Association named 'grand_parent' was not found on Child; perhaps you misspelled it? – Bailey Smith Mar 10 '16 at 03:36
  • oops sorry `.joins(parent: [:grand_parent])` – bf34 Mar 10 '16 at 03:38
  • @bf34 thank you - what if you want to find children which do not have an associated grandparent (i.e. where the grandparent is nil)?? – BenKoshy Aug 08 '16 at 02:26
  • @BKSpurgeon for that you can use something like.... `Child.joins(:parent).where('not exists(select 1 from grand_parents where grand_parents.id = parents.grand_parent_id')` it would be slightly different if there's a join table in between like a `grand_parent_parents` table `Child.joins(:parent).where('not exists(select 1 from grand_parent_parents where grand_parent_parents.parent_id = parent.id')` – bf34 Aug 09 '16 at 11:08
  • @bf34 awesome thank you - where can i learn how to do the more complex stuff like this? – BenKoshy Aug 09 '16 at 12:26
  • Can you clarify this answer, e.g. what if I want all child records for one particular grandparent record? – Mirror318 Dec 11 '16 at 22:26
  • `Project.joins(user: [:organisation]).where('organisation.id is 2')` complains about a MySQL syntax error – Mirror318 Dec 11 '16 at 22:37
  • you need to pluralize the table name I believe. so it should be `where('organisations.id is 2')` rather than `organisation.id` – bf34 Dec 12 '16 at 23:25