Rails - May 2006 - 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060519/34e7f1a2/attachment.html

Mariano Kamp wrote:
>    I have the following code:
>          @conversation_pages,
>           @conversations = paginate(:conversations,
>                                     :per_page => 20,
>                                     :joins => ''LEFT JOIN
messages on
> messages.conversation_id = conversations.id <http://conversations.id>
''+
>                                               ''LEFT JOIN
email_addresses
> on email_addresses.id = messages.email_address_id'',
>                                     :conditions => 
> [''email_addresses.email = ?'', @email_address.email])
> 
>   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.


-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---