JoinDependency.instantiate & Non-unique association names

Hello,

I'm instantiating the results of a multi-table, find_by_sql query. I
start by creating an association graph as an argument to
JoinDependency. The instantiation fails if the graph contains any
duplicate association names, but it works if the associations are
unique. (The sql query executes correctly even when instantiation
fails)

I can't think of a reason for an association name to be unique across
different models in a graph, so I'm wondering if this is a bug?

Thanks,
Tom

Here's an example of the problem:

#Create tables
create table a_foos(id int(11) DEFAULT NULL auto_increment PRIMARY
KEY, name varchar(255));
create table b_foos(id int(11) DEFAULT NULL auto_increment PRIMARY
KEY, name varchar(255), a_foo_id int, c_foo_id int, e_foo_id int);
create table c_foos(id int(11) DEFAULT NULL auto_increment PRIMARY
KEY, name varchar(255),type varchar(255));
create table d_foos(id int(11) DEFAULT NULL auto_increment PRIMARY
KEY, name varchar(255), c_foo_id int, e_foo_id int);
create table e_foos(id int(11) DEFAULT NULL auto_increment PRIMARY
KEY, name varchar(255));

#Define models
class AFoo < ActiveRecord::Base
  has_many :b_foos
end
class BFoo < ActiveRecord::Base
  belongs_to :a_foo
  belongs_to :c_foo
  belongs_to :e_foo
end
class CFoo < ActiveRecord::Base
  has_many :b_foos
  has_many :d_foos
end
class DFoo < ActiveRecord::Base
  belongs_to :c_foo
  belongs_to :d_foo
end
class EFoo < ActiveRecord::Base
  has_many :b_foos
  has_many :d_foos
end

#Create partial data
a_foo = AFoo.new(:name=>"some AFoo")
a_foo.save!
b_foo = BFoo.new(:name=>"some BFoo", :a_foo=>a_foo)
b_foo.save!

#Setup Join Dependency and create SQL Query
assoc_graph = [{:b_foos=>[{:c_foo=>[:d_foos]}, {:e_foo=>[:d_foos]}]}]
#association, :d_foos, exists on two models in graph
join_dep =
ActiveRecord::Associations::ClassMethods::JoinDependency.new(AFoo,
assoc_graph, nil)

sql_selects = []
sql_joins = []
for i in 0..join_dep.join_associations.size-1
  assoc = join_dep.join_associations[i]
  sql_selects += assoc.parent.column_names_with_alias.collect{ |map|
"#{assoc.parent.aliased_table_name}.`#{map[0]}` AS #{map[1]}" } if
i==0 #base
  sql_selects += assoc.column_names_with_alias.collect{ |map|
"#{assoc.aliased_table_name}.`#{map[0]}` AS #{map[1]}" }
  sql_joins << assoc.association_join
end
query = "
SELECT #{sql_selects.join(",\n ")}
FROM #{join_dep.join_associations[0].parent.table_name}
#{join_dep.join_associations[0].parent.aliased_table_name}
#{sql_joins.join("\n")}
"

#The query runs correctly
#+-------+-----------+-------+-----------+-------+...
#| t0_r0 | t0_r1 | t1_r0 | t1_r1 | t1_r2 |...
#+-------+-----------+-------+-----------+-------+...
#| 1 | some AFoo | 1 | some BFoo | 1 |...
#+-------+-----------+-------+-----------+-------+...

results = AFoo.find_by_sql(query)
=> [#<AFoo >]

eager_afoos = join_dep.instantiate(results)
NoMethodError: undefined method `d_foos' for #<BFoo:0x2a27978>
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
attribute_methods.rb:205:in `method_missing'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1486:in `send'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1486:in `construct_association'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1475:in `construct'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1474:in `each'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1474:in `construct'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1471:in `construct'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1470:in `each'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1470:in `construct'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1476:in `construct'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1474:in `each'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1474:in `construct'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1471:in `construct'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1470:in `each'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1470:in `construct'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1402:in `instantiate'
  from (irb):41:in `each_with_index'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1397:in `each'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1397:in `each_with_index'
  from /tmp/Main/vendor/rails/activerecord/lib/active_record/
associations.rb:1397:in `instantiate'
  from (irb):41

Instantiation works if I use an assoc_graph without multiple :d_foos
associations:
assoc_graph = [{:b_foos=>[{:c_foo=>[:d_foos]}, {:e_foo=>[:d_foos]}]}]
vs
assoc_graph = [{:b_foos=>[:c_foo, {:e_foo=>[:d_foos]}]}]

Ok, this has nothing to do with the duplicate :d_foos association.

The problem has to do with the fact that no c/d/e_foo records exist.

associations.rb:
construct_association() returns nil when
row[join.aliased_primary_key].nil, meaning there is no data from the
left outer join.

I think construct() should call joins.shift N times when
construct_association() returns nil, where N is the number of
descendent associations.

def construct(parent, associations, joins, row)
...
    when Hash
      associations.keys.sort{|a,b|a.to_s<=>b.to_s}.each do |name|
        association = construct_association(parent, joins.shift, row)
        construct(association, associations[name], joins, row) if
association #else we might need to shift joins?
      end
    else
...

tom_302 wrote:

Ok, this has nothing to do with the duplicate :d_foos association.

The problem has to do with the fact that no c/d/e_foo records exist.

associations.rb:
construct_association() returns nil when
row[join.aliased_primary_key].nil, meaning there is no data from the
left outer join.

I think construct() should call joins.shift N times when
construct_association() returns nil, where N is the number of
descendent associations.

def construct(parent, associations, joins, row)
...
    when Hash
      associations.keys.sort{|a,b|a.to_s<=>b.to_s}.each do |name|
        association = construct_association(parent, joins.shift, row)
        construct(association, associations[name], joins, row) if
association #else we might need to shift joins?
      end
    else
...

May I ask why you are needing to use find_by_sql and custom
association construction? Is it because the query cannot
be expressed as a normal AR find, or is it because you want to
narrow the attributes selected?

If the former, can you do the eager loading by properly aliasing
the fields? e.g. http://mrj.bpa.nu/eager_custom_sql_rails_1.2.rb

If the latter, you may want to check out my plugin that allows
field/attribute selection for eager-loaded associations:

  http://dev.rubyonrails.org/attachment/ticket/7147/init.5.rb

The construct method in this plugin does what you suggest above,
following the recursion even for absent associations.