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

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
838869251cf09420cc80a9e008adddc1?d=identicon&s=25 Mariano Kamp (Guest)
on 2006-05-19 16:56
(Received via mailing list)
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
C64e63b70be7dfed8b0742540b8b27e5?d=identicon&s=25 Mark Reginald James (Guest)
on 2006-05-20 13:39
(Received via mailing list)
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.
09e6ba06911a5781c04be8554f114aba?d=identicon&s=25 Rob Nichols (reggieb)
on 2006-08-22 14:16
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.
This topic is locked and can not be replied to.