Ruby Forum Ruby on Rails > Problem with joins and overlapping field names

Hi,

   I have the following code:
         @conversation_pages,
          @conversations = paginate(:conversations,
                                    :per_page => 20,
                                    :joins => 'LEFT JOIN messages on
messages.conversation_id = conversations.id '+
                                              'LEFT JOIN email_addresses 
on
email_addresses.id = messages.email_address_id',
                                    :conditions => 
['email_addresses.email =
?', @email_address.email])

  producing this sql staement:

SELECT * FROM conversations LEFT JOIN messages on 
messages.conversation_id =
conversations.id LEFT JOIN email_addresses on email_addresses.id =
messages.email_address_id WHERE (email_addresses.email = 
'xyz@gmail.com')
LIMIT 0, 20

  The funny thing is that my conversation.id is no longer referencing 
the
conversation.id but email_addresses.id. I assume this is due to the fact
that "*" is generated in the sql, isn't it? I haven't found a way to
instruct active record to use explicit column names like 
conversations.id,
email_addressesses.id etc. etc.

  Any idea how to work around that problem?

Cheers,
Mariano

Mariano Kamp wrote:
> 
>   The funny thing is that my conversation.id <http://conversation.id> is 
> no longer referencing the conversation.id <http://conversation.id> but 
> email_addresses.id. I assume this is due to the fact that "*" is 
> generated in the sql, isn't it? I haven't found a way to instruct active 
> record to use explicit column names like conversations.id 
> <http://conversations.id>, email_addressesses.id etc. etc.
> 
>   Any idea how to work around that problem?


Try either replacing the joins option with
   :include => {:messages => :email_address}

or use a :select option to select only the fields you want. e.g.
   :select => 'conversations.*, messages.content, email_addresses.email'

--
We develop, watch us RoR, in numbers too big to ignore.

Mark Reginald James wrote:
> Mariano Kamp wrote:
>> 
>>   The funny thing is that my conversation.id <http://conversation.id> is 
>> no longer referencing the conversation.id <http://conversation.id> but 
>> email_addresses.id. > 
> 
> Try either replacing the joins option with
>    :include => {:messages => :email_address}
> 
> or use a :select option to select only the fields you want. e.g.
>    :select => 'conversations.*, messages.content, email_addresses.email'
> 

I had the same problem. My tables are associated like this:

stocks = each item of stock (belongs_to product)
products = all the products we sell (belongs_to product_type and 
has_many stocks)
product_types = a grouping of products (has_many products)

I was using:

    @stocks = Stock.find(:all,
                          :conditions => "pr.product_type_id = 
#{@product_type.id}",
                          :joins => "as st right join products as pr on 
st.product_id = pr.id")

The result was that when I tried to use stock.id in the resulting view, 
the system was returning the product.id. Having just found this posting, 
I updated the @stocks declaration to:

    @stocks = Stock.find(:all,
                          :select => "st.*",
                          :conditions => "pr.product_type_id = 
#{@product_type.id}",
                          :joins => "as st right join products as pr on 
st.product_id = pr.id")

The :select => "st.*" entry did the trick. stock.id is now returning the 
correct id.

Thank you Mark.