Problem with joins and overlapping field names


#1

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 =
‘removed_email_address@domain.invalid’)
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


#2

Mariano K. 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.


#3

Mark Reginald J. wrote:

Mariano K. 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.