Object with wrong id after named_scope

Question

The use case

I have 3 models :

• a Message
• a Conversation
• a MessageRecipient

The relations are defined by

• A message has_and_belongs_to_many conversations (and inversely)
• A message has_many message_recipients
• A MessageRecipient belongs_to a message and a recipient (not defined here)

The case it works

The method

@recipient.inbox_conversations.first.messages

create the following SQL query :

SELECT *
FROM `messages`
INNER JOIN `conversations_messages`
  ON `messages`.id = `conversations_messages`.message_id
WHERE (`conversations_messages`.conversation_id = 2061 ) 

The result is the expected message :

[#<Message id: 7045, ..>]

The problem

The next method (just the same than previous one, with a additional named scope)

@recipient.inbox_conversations.first.messages.received_by(@recipient)

create the following SQL query :

SELECT * FROM `messages`
INNER JOIN `message_recipients`
  ON `message_recipients`.message_id = `messages`.id
INNER JOIN `conversations_messages`
  ON `conversations_messages`.message_id = `messages`.id
WHERE (`conversations_messages`.conversation_id = 2060 )
  AND (`message_recipients`.recipient_id = 32363)

Which returns me an inexisting ActiveRecord element

[#<Message id: 9025, ..>]

I tried to do a Message.find_by_id(9025) just after this line, this returns nil.

You want some additional code ?

class Message

named_scope :received_by, lambda { |recipient| {
  :conditions => [ "`#{MessageRecipient.table_name}`.recipient_id = ?", recipient.id ],
  :joins => "INNER JOIN `#{MessageRecipient.table_name}`
   ON `#{MessageRecipient.table_name}`.message_id = `#{Message.table_name}`.id"
} }

.

class Receiver

def inbox_conversations
  inbox_messages = Message.received_by(self).find(:all, :include => :conversations)
  return inbox_messages.map(&:conversations).flatten.uniq
end

Thanks for reading this !

Answer 1

The problem is that rails 2 isn't particularly smart when 2 or more columns in the result set have the same name: rails only looks at the column name so these will overwrite each other. In your case the id from one of the joined tables is shadowing the id from the messages table.

You need to add a select option to your scope to either only retrieve columns from the messages table (:select => 'messages.*') or if you do need the columns from the joined tables then only select the ones you need and be sure to alias any conflicting column names.