Finding parents with red-haired children?

Here's the basic situation

  class Person < ActiveRecord::Base     acts_as_tree     belongs_to :hair_color     validates_presence_of :name   end

  class HairColor < ActiveRecord::Base     validates_presence_of :name   end

What I want to achieve:

I need another association on Person that returns only the red-haired children. In my special case[*] it has to be an association, an association extension, named scope or similar won't do. In particular, this association has to work when listing all persons and their red-headed children and possibly with a filter condition.

A first stab looks like this

  class Person < ActiveRecord::Base     ...     has_many :redheads, :class_name => 'Person',       :foreign_key => :parent_id,       :include => :hair_color,       :conditions => "hair_colors.name = 'red'"   end

This works indeed for a case like this

  p = Person.find(:first)   p.redheads

however, it doesn't work for

  parents = Person.find(:all, :include => :redheads)

In that case, the secondary assocation (redheads.hair_color) doesn't even seem to be included. Adding it manually like this

  parents = Person.find(:all, :include => [:redheads, :hair_color])

doesn't help because the "scoping" is wrong: hair_color is joined on the parents, not the children.

So, is there a way to make ActiveRecord do what I need without resorting to hand-written SQL?

Michael

[*] I already have the infrastructure to display and filter lists of objects with associated objects *one-level* deep. E.g., for a list of persons I could define a column { :column => 'hair_color.name', :title => 'Hair color' }. I'd like to specify a column such as { :column => 'redheads.name', :title => 'Redheads' }.

What about Red-Headed step children?

sorry, couldn't help it

however, it doesn't work for

parents = Person.find(:all, :include => :redheads)

In that case, the secondary assocation (redheads.hair_color) doesn't even seem to be included. Adding it manually like this

parents = Person.find(:all, :include => [:redheads, :hair_color])

  parents = Person.find(:all, :include => {:redheads => :hair_color}) ? Fred

What about 2 steps

@parent_ids = find(:all, :conditions => "hair_colors.name = 'red'"} @red_head_parents = find(:all, :conditions => ":id IN @parent_ids.uniq'"}

I prolly mucked the syntax, but ...

lets try that again

redheads = Person.find(:all, :conditions => "hair_colors.name = 'red'"} parent_ids = redheads .inject() do {|parent_list, redheads.parent_id| parent_list<<redheads.parent_id} red_head_parents = Person.find(:all, :conditions => ":id IN parent_ids'"}

And I still prolly mucked it up

> however, it doesn't work for > > parents = Person.find(:all, :include => :redheads) > > In that case, the secondary assocation (redheads.hair_color) > doesn't even seem to be included. Adding it manually like this > > parents = Person.find(:all, :include => [:redheads, :hair_color])

  parents = Person.find(:all, :include => {:redheads => :hair_color}) ?

No, unfortunately not. I have a test dataset with 20 persons -- 4 parents + 16 children -- and a "random" assignment of hair colors.

ps = Person.find(:all, :include => { :redheads => :hair_color }) ps.map { |p| p.redheads.map(&:hair_color).map(&:name) }

=> [["black", "black", "blonde", "brown"], ["brown", "black", "red", "blonde"], ["blonde", "red", "brown", "brown"], ["brown", "black", "brown", "blonde"], , , , , , , , , , , , , , , , ]

When I include the parent's hair color, too, the result deteriorates

ps = Person.find(:all, :include => [:hair_color, { :redheads

=> :hair_color }])

ps.map { |p| p.redheads.map(&:hair_color).map(&:name) }

=> [, , , ["brown", "black", "brown", "blonde"], , , , , , , , , , , , , , , , ]

I'm not too fond of dissecting the generated SQL and especially why it was generated like that. From a somewhat superficial vantage point I'd say that the cause of the trouble lies with joining at the correct level, that is children, not parents. IIRC, the association code was slated for a thorough revision for Rails 3.0, I may just wait for it.

Michael

Yes, on two counts. First, I'm not concerned with red-haired parents, but with their children. Second, I want to retrieve *all* parents with their red-haired children (if any) included.

As all this is going to be displayed in a list with optional filter conditions, I may need to filter by the parent's name or hair color as well as by a child's name.

Just consider the case where you have a paginated list of all people in your db and you want to filter it down to only the red-haired people who have a red-haired child named John.

Michael

however, it doesn't work for

parents = Person.find(:all, :include => :redheads)

In that case, the secondary assocation (redheads.hair_color) doesn't even seem to be included. Adding it manually like this

parents = Person.find(:all, :include => [:redheads, :hair_color])

parents = Person.find(:all, :include => {:redheads => :hair_color}) ?

No, unfortunately not. I have a test dataset with 20 persons -- 4 parents + 16 children -- and a "random" assignment of hair colors.

ps = Person.find(:all, :include => { :redheads => :hair_color }) ps.map { |p| p.redheads.map(&:hair_color).map(&:name) }

=> [["black", "black", "blonde", "brown"], ["brown", "black", "red", "blonde"], ["blonde", "red", "brown", "brown"], ["brown", "black", "brown", "blonde"], , , , , , , , , , , , , , , , ]

When I include the parent's hair color, too, the result deteriorates

Ah yes, conditions on :include are a bit iffy and often give
unexpected consequences. Rails 2.1 has a rewrite of eager loading but
it may not help you

Could you do it the otherway round i.e. something like
red_color.find :people, :include => :parent That gives you all redheads with their parents. Then (can't think of a
better verb) transpose/invert this to have it by parent.

This may be one of those times when writing some grungy sql is the way
forward.

Fred

No, that doesn't work because I want to list *all* persons but specifically include the red-haired children in the list. A more practical reason is that it does not fit with my current infrastructure that nicely handles all the easier cases.

Incidentally, I'm not preoccupied with redheads, apart from myself having a hair color in that general direction. It's just that this somewhat contorted example perfectly fits my real problem.

Michael

No, that doesn't work because I want to list *all* persons but specifically include the red-haired children in the list. A more practical reason is that it does not fit with my current infrastructure that nicely handles all the easier cases.

Incidentally, I'm not preoccupied with redheads, apart from myself having a hair color in that general direction. It's just that this somewhat contorted example perfectly fits my real problem.

Did you ever figure this one out? I in a similar situation. Basically I am trying to fake inheritance with a belongs_to relationship, which means that if I want to search by any of the columns that come from the "parent" object I have to do it outside of the database.

Anyway, if I figure anything out I'll note it here.

-fREW