#835 confirmed
sausheong (at gmail)

Customization self-referencing association

Reported by sausheong (at gmail) | April 19th, 2009 @ 03:40 AM

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`

Comments and changes to this ticket

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket

Tags

Pages