Modification to /lib/dm-core/adapters/data_objects_adapter.rb

Explanation

The two stack traces you see below indicate DataMapper's failure to respect multiple joins.

The DB schema we created is as follows:

Bill:
has n, RollCall
has n, Bill::Title

As it became apparent, DM wasn't properly identifying the table to join to the query.

RollCall.all("bill.titles.text.like" => "%Ledbetter%") =>
SELECT [columns exerpted] FROM "roll_calls" 
INNER JOIN "bills" ON ("bills"."id" = "roll_calls"."bill_id") 
INNER JOIN "bills" ON ("bills"."id" = "bill_titles"."bill_id") 
WHERE ("bill_titles"."text" LIKE '%Ledbetter%') ORDER BY "roll_calls"."id"
 ~ no such column: bill_titles.bill_id

>> Bill.all("titles.text.like" => "%Ledbetter%").final_roll =>
SELECT [columns excerpted] FROM "roll_calls" 
INNER JOIN "bills" ON ("roll_calls"."id" = "bills"."final_roll_id") 
INNER JOIN "bills" ON ("bills"."id" = "bill_titles"."bill_id") 
WHERE ("bill_titles"."text" LIKE '%Ledbetter%') ORDER BY "roll_calls"."id"
 ~ no such column: bill_titles.bill_id

As you can see in the former case, the select is on the "roll_calls" table (correct), 
joins the "bills" table via belongs_to relationship (correct for the moment), 
and then incorrectly joins the "bills" table again (incorrectly getting "bills" 
out of the Bill has n, Bill::Title relationship).

The latter case is even more egregious.  The hosting table is "bills", to which 
"bill_titles" should be joined (instead of "bills"), and then "roll_calls" should 
be joined (instead of the second "bills").

The patch at the bottom of this list of files allows for not only chaining joins, 
will allow for any arbitrary connection of joins in the list of tables being joined together.
It does this by keeping a list of tables involved in the join.  The list of tables is necessary
in the event of a long distance dependency (e.g. 
"a INNER JOIN b ON (a.id = b.a_id) 
INNER JOIN c ON (b.id = c.b_id) 
INNER JOIN d (a.id = d.a_id)").

It should be noted that the parent-child relationship specified by each association 
is irrelevant for equijoins, but is relevant for other sorts of joins.  
When DM begins to offer those sorts of joins this will have to be revised to 
take that into account.

Failure of Condition with 2 Chained Joins

>> RollCall.all("bill.titles.text.like" => "%Ledbetter%")
 ~ (0.028033) SELECT "roll_calls"."id", "roll_calls"."congress", "roll_calls"."session", "roll_calls"."chamber", "roll_calls"."roll_call", "roll_calls"."voted_at", "roll_calls"."bill_type", "roll_calls"."bill_number", "roll_calls"."passage_requirements", "roll_calls"."vote_type", "roll_calls"."question", "roll_calls"."description", "roll_calls"."result", "roll_calls"."bill_id" FROM "roll_calls" INNER JOIN "bills" ON ("bills"."id" = "roll_calls"."bill_id") INNER JOIN "bills" ON ("bills"."id" = "bill_titles"."bill_id") WHERE ("bill_titles"."text" LIKE '%Ledbetter%') ORDER BY "roll_calls"."id"
 ~ no such column: bill_titles.bill_id
Query: SELECT "roll_calls"."id", "roll_calls"."congress", "roll_calls"."session", "roll_calls"."chamber", "roll_calls"."roll_call", "roll_calls"."voted_at", "roll_calls"."bill_type", "roll_calls"."bill_number", "roll_calls"."passage_requirements", "roll_calls"."vote_type", "roll_calls"."question", "roll_calls"."description", "roll_calls"."result", "roll_calls"."bill_id" FROM "roll_calls" INNER JOIN "bills" ON ("bills"."id" = "roll_calls"."bill_id") INNER JOIN "bills" ON ("bills"."id" = "bill_titles"."bill_id") WHERE ("bill_titles"."text" LIKE '%Ledbetter%') ORDER BY "roll_calls"."id"
Sqlite3Error: no such column: bill_titles.bill_id
Query: SELECT "roll_calls"."id", "roll_calls"."congress", "roll_calls"."session", "roll_calls"."chamber", "roll_calls"."roll_call", "roll_calls"."voted_at", "roll_calls"."bill_type", "roll_calls"."bill_number", "roll_calls"."passage_requirements", "roll_calls"."vote_type", "roll_calls"."question", "roll_calls"."description", "roll_calls"."result", "roll_calls"."bill_id" FROM "roll_calls" INNER JOIN "bills" ON ("bills"."id" = "roll_calls"."bill_id") INNER JOIN "bills" ON ("bills"."id" = "bill_titles"."bill_id") WHERE ("bill_titles"."text" LIKE '%Ledbetter%') ORDER BY "roll_calls"."id"
	from /Library/Ruby/Gems/1.8/gems/dm-core-0.9.10/lib/dm-core/adapters/data_objects_adapter.rb:48:in `execute_reader'
	from /Library/Ruby/Gems/1.8/gems/dm-core-0.9.10/lib/dm-core/adapters/data_objects_adapter.rb:48:in `read_many'
	from /Library/Ruby/Gems/1.8/gems/dm-core-0.9.10/lib/dm-core/adapters/data_objects_adapter.rb:173:in `with_connection'
	from /Library/Ruby/Gems/1.8/gems/dm-core-0.9.10/lib/dm-core/adapters/data_objects_adapter.rb:40:in `read_many'
	from /Library/Ruby/Gems/1.8/gems/extlib-0.9.10/lib/extlib/lazy_array.rb:415:in `[]'
	from /Library/Ruby/Gems/1.8/gems/extlib-0.9.10/lib/extlib/lazy_array.rb:415:in `lazy_load'
	from /Library/Ruby/Gems/1.8/gems/extlib-0.9.10/lib/extlib/lazy_array.rb:7:in `inspect'
	from /Library/Ruby/Gems/1.8/gems/wirble-0.1.2/./wirble.rb:417:in `output_value'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:151:in `eval_input'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:259:in `signal_status'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:147:in `eval_input'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:146:in `eval_input'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:70:in `start'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:69:in `catch'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:69:in `start'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/lib/merb-core/rack/adapter/irb.rb:161:in `start'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/lib/merb-core/server.rb:174:in `bootup'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/lib/merb-core/server.rb:42:in `start'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/lib/merb-core.rb:170:in `start'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/bin/merb:11
	from /usr/bin/merb:19:in `load'
	from /usr/bin/merb:19

Failure of implicit 2nd chained join

>> Bill.all("titles.text.like" => "%Ledbetter%").final_roll
 ~ (0.000739) SELECT "roll_calls"."id", "roll_calls"."congress", "roll_calls"."session", "roll_calls"."chamber", "roll_calls"."roll_call", "roll_calls"."voted_at", "roll_calls"."bill_type", "roll_calls"."bill_number", "roll_calls"."passage_requirements", "roll_calls"."vote_type", "roll_calls"."question", "roll_calls"."description", "roll_calls"."result", "roll_calls"."bill_id" FROM "roll_calls" INNER JOIN "bills" ON ("roll_calls"."id" = "bills"."final_roll_id") INNER JOIN "bills" ON ("bills"."id" = "bill_titles"."bill_id") WHERE ("bill_titles"."text" LIKE '%Ledbetter%') ORDER BY "roll_calls"."id"
 ~ no such column: bill_titles.bill_id
Query: SELECT "roll_calls"."id", "roll_calls"."congress", "roll_calls"."session", "roll_calls"."chamber", "roll_calls"."roll_call", "roll_calls"."voted_at", "roll_calls"."bill_type", "roll_calls"."bill_number", "roll_calls"."passage_requirements", "roll_calls"."vote_type", "roll_calls"."question", "roll_calls"."description", "roll_calls"."result", "roll_calls"."bill_id" FROM "roll_calls" INNER JOIN "bills" ON ("roll_calls"."id" = "bills"."final_roll_id") INNER JOIN "bills" ON ("bills"."id" = "bill_titles"."bill_id") WHERE ("bill_titles"."text" LIKE '%Ledbetter%') ORDER BY "roll_calls"."id"
Sqlite3Error: no such column: bill_titles.bill_id
Query: SELECT "roll_calls"."id", "roll_calls"."congress", "roll_calls"."session", "roll_calls"."chamber", "roll_calls"."roll_call", "roll_calls"."voted_at", "roll_calls"."bill_type", "roll_calls"."bill_number", "roll_calls"."passage_requirements", "roll_calls"."vote_type", "roll_calls"."question", "roll_calls"."description", "roll_calls"."result", "roll_calls"."bill_id" FROM "roll_calls" INNER JOIN "bills" ON ("roll_calls"."id" = "bills"."final_roll_id") INNER JOIN "bills" ON ("bills"."id" = "bill_titles"."bill_id") WHERE ("bill_titles"."text" LIKE '%Ledbetter%') ORDER BY "roll_calls"."id"
	from /Library/Ruby/Gems/1.8/gems/dm-core-0.9.10/lib/dm-core/adapters/data_objects_adapter.rb:48:in `execute_reader'
	from /Library/Ruby/Gems/1.8/gems/dm-core-0.9.10/lib/dm-core/adapters/data_objects_adapter.rb:48:in `read_many'
	from /Library/Ruby/Gems/1.8/gems/dm-core-0.9.10/lib/dm-core/adapters/data_objects_adapter.rb:173:in `with_connection'
	from /Library/Ruby/Gems/1.8/gems/dm-core-0.9.10/lib/dm-core/adapters/data_objects_adapter.rb:40:in `read_many'
	from /Library/Ruby/Gems/1.8/gems/extlib-0.9.10/lib/extlib/lazy_array.rb:415:in `[]'
	from /Library/Ruby/Gems/1.8/gems/extlib-0.9.10/lib/extlib/lazy_array.rb:415:in `lazy_load'
	from /Library/Ruby/Gems/1.8/gems/extlib-0.9.10/lib/extlib/lazy_array.rb:7:in `inspect'
	from /Library/Ruby/Gems/1.8/gems/wirble-0.1.2/./wirble.rb:417:in `output_value'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:151:in `eval_input'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:259:in `signal_status'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:147:in `eval_input'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:146:in `eval_input'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:70:in `start'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:69:in `catch'
	from /System/Library/Frameworks/Ruby.framework/Versions/1.8/usr/lib/ruby/1.8/irb.rb:69:in `start'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/lib/merb-core/rack/adapter/irb.rb:161:in `start'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/lib/merb-core/server.rb:174:in `bootup'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/lib/merb-core/server.rb:42:in `start'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/lib/merb-core.rb:170:in `start'
	from /Library/Ruby/Gems/1.8/gems/merb-core-1.0.8.1/bin/merb:11
	from /usr/bin/merb:19:in `load'
	from /usr/bin/merb:19

gistfile4.diff

         def links_statement(query)
-          table_name = query.model.storage_name(query.repository.name)
+          table_list = [query.model.storage_name(query.repository.name)]
 
           statement = ''
           query.links.each do |relationship|
             parent_table_name = relationship.parent_model.storage_name(query.repository.name)
             child_table_name  = relationship.child_model.storage_name(query.repository.name)
 
-            join_table_name = table_name == parent_table_name ? child_table_name : parent_table_name
+            join_table_name = if table_list.include?(parent_table_name)
+                                child_table_name
+                              elsif table_list.include?(child_table_name)
+                                parent_table_name
+                              else
+                                raise ArgumentError "you're trying to join a table with no connection to this query"
+                              end
+            table_list << join_table_name
 
             # We only do INNER JOIN for now
             statement << " INNER JOIN #{quote_table_name(join_table_name)} ON "