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’ }.


Michael S.
mailto:[email protected]
http://www.schuerig.de/michael/

What about Red-Headed step children?

sorry, couldn’t help it

On 20 May 2008, at 17:21, Michael S. wrote:

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 …

On May 20, 9:52 am, Frederick C. [email protected]

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

On Tuesday 20 May 2008, Frederick C. wrote:

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


Michael S.
mailto:[email protected]
http://www.schuerig.de/michael/

On 20 May 2008, at 19:05, Michael S. wrote:

parents = Person.find(:all, :include => [:redheads, :hair_color])
[“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

On Tuesday 20 May 2008, Ruby F. wrote:

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

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


Michael S.
mailto:[email protected]
http://www.schuerig.de/michael/

On Tuesday 20 May 2008, Frederick C. wrote:

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.

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


Michael S.
mailto:[email protected]
http://www.schuerig.de/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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs