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