Resolving ambiguous columns during a join


#1

I am working with an existing schema and thus limited in how much I can
bend my existing column names. I have a table Users, and a table
ForumUsers, for which I have a “has_one” relationship - a user has_one
forumuser. When I look at my user records I wish to include some
information from forumuser:

@users = User.find :all, :include => :forum_user, :order => “email” …

However, both these tables have a “status” column, and I end up getting
ambiguous column reference errors.

Could someone please tell me how and where I can either tell forumuser
not to load up its status field at all (I don’t need it for this case),
or else rename it to resolve the conflict? The error occurs at the
“:include => :forum_user” reference, so perhaps is there something I can
specify there that says “only include attributes x,y,z from forum_user”?

Thank you!


#2

Duane wrote:

I am working with an existing schema and thus limited in how much I can
bend my existing column names. I have a table Users, and a table
ForumUsers, for which I have a “has_one” relationship - a user has_one
forumuser. When I look at my user records I wish to include some
information from forumuser:

@users = User.find :all, :include => :forum_user, :order => “email” …

However, both these tables have a “status” column, and I end up getting
ambiguous column reference errors.

Could someone please tell me how and where I can either tell forumuser
not to load up its status field at all (I don’t need it for this case),
or else rename it to resolve the conflict? The error occurs at the
“:include => :forum_user” reference, so perhaps is there something I can
specify there that says “only include attributes x,y,z from forum_user”?

Thank you!

Duane, here is an example.


My test schema - schema.rb

This file is autogenerated. Instead of editing this file, please use

the

migrations feature of ActiveRecord to incrementally modify your

database, and

then regenerate this schema definition.

ActiveRecord::Schema.define() do

create_table “forum_users”, :force => true do |t|
t.column “name”, :string, :limit => 50
t.column “status”, :boolean, :default => false
t.column “user_id”, :integer, :default => 0, :null => false
end

create_table “users”, :force => true do |t|
t.column “name”, :string, :limit => 50
t.column “status”, :boolean, :default => false
end

end

In my test, the user has a status of 0 and the forum user has a status
of 1.

u = User.find(1)
=> #<User:0x23431ec @attributes={“status”=>“0”, “name”=>“Damon”,
“id”=>“1”}>

u.forum_user
=> #<ForumUser:0x233e4f8 @attributes={“status”=>“1”, “name”=>“DC
Scales”, “id”=>“1”, “user_id”=>“1”}>

User Load (0.001579) SELECT * FROM users WHERE (users.id = 1) LIMIT
1
User Columns (0.034679) SHOW FIELDS FROM users
ForumUser Load (0.001829) SELECT * FROM forum_users WHERE
(forum_users.user_id = 1) LIMIT 1


users = users = User.find(:all, :include => :forum_user)
=> [#<User:0x23376a8 @attributes={“status”=>“0”, “name”=>“Damon”,
“id”=>“1”}, @forum_user=#<ForumUser:0x23374f0
@attributes={“status”=>“1”, “name”=>“DC Scales”, “id”=>“1”,
“user_id”=>“1”}>>]

User Columns (0.004300) SHOW FIELDS FROM users
ForumUser Columns (0.003337) SHOW FIELDS FROM forum_users
User Load Including Associations (0.027452) SELECT forum_users.id
AS t1_r0, forum_users.name AS t1_r1, users.id AS t0_r0,
forum_users.status AS t1_r2, users.name AS t0_r1,
forum_users.user_id AS t1_r3, users.status AS t0_r2 FROM users LEFT
OUTER JOIN forum_users ON forum_users.user_id = users.id

u = users[0]
=> #<User:0x23376a8 @attributes={“status”=>“0”, “name”=>“Damon”,
“id”=>“1”}, @forum_user=#<ForumUser:0x23374f0
@attributes={“status”=>“1”, “name”=>“DC Scales”, “id”=>“1”,
“user_id”=>“1”}>>

u.forum_user.status
=> true

u.status
=> false

So there can be no ambiguity if you are accessing the object directly.
Problem solved.

However, if you are for some reason being forced to use find_by_sql,
you’ll want to use “AS” in your SQL to refer to it by another name.

For example,

users = User.find_by_sql(“select u.name, u.status, u.status as
userstatus, fu.name, fu.status,fu.status as forumstatus from users u,
forum_users fu where fu.user_id = u.id”)
=> [#<User:0x2336780 @attributes={“status”=>“1”, “name”=>“DC Scales”,
“userstatus”=>“0”, “forumstatus”=>“1”}>]

u = users[0]
=> #<User:0x2336780 @attributes={“status”=>“1”, “name”=>“DC Scales”,
“userstatus”=>“0”, “forumstatus”=>“1”}>

puts u.userstatus, u.status, u.forumstatus
0
true
1

You can see that the status field in our result has been clobbered by
the forum user’s status. However, we’ve got access to the user status
with our “u.userstatus” reference.

Interesting that the u.status gets converted to a boolean but our
aliased fields are unchanged ( “0” and “1” ).

HTH,
-damon
http://damonclinkscales.com/