Customization self-referencing association

Question

In a data model where associations are customized, doing a query on a self-reference association causes a bug. For example, in this model:

class User
  include DataMapper::Resource

  property :id,     Serial
  property :name,   String, :length => 255
end

class Message
  include DataMapper::Resource

  property :id, Serial
  property :text, String, :length => 255
  property :created_at,  DateTime  
  belongs_to :recipient, :class_name => "User", :child_key => [:recipient_id]
  belongs_to :user 
end

Doing something like this:

Message.all('user.id' => user1.id, 'recipient.id' => user2.id)

results in this error:

MysqlError: (mysql_errno=1066, sql_state=42000) Not unique table/alias: 'users'
Query: SELECT `messages`.`id`, `messages`.`text`, `messages`.`created_at`, `messages`.`user_id`, `messages`.`recipient_id` FROM `messages` INNER JOIN `users` ON (`users`.`id` = `messages`.`user_id`) INNER JOIN `users` ON (`users`.`id` = `messages`.`recipient_id`) WHERE (`users`.`id` = 1) AND (`users`.`id` = 2) ORDER BY `messages`.`id`

This is the SQL that should have been generated:

SELECT `messages`.`id`, `messages`.`text`, `messages`.`created_at`, `messages`.`user_id`, `messages`.`recipient_id` FROM `messages` INNER JOIN `users` as u1 ON (u1.`id` = `messages`.`user_id`) INNER JOIN `users` as u2 ON (u2.`id` = `messages`.`recipient_id`) WHERE (u1.`id` = 1) and (u2.id = 2) ORDER BY `messages`.`id`

Created by sausheong (at gmail) - 2009-04-19 08:40:07 UTC

Original Lighthouse ticket: http://datamapper.lighthouseapp.com/projects/20609/tickets/835

该提问来源于开源项目：datamapper/dm-core

Answer 1

[project:id#20609 not-tagged:"0.10.0" milestone:id#51895 bulk edit command]

by Dan Kubb (dkubb)