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/
--
Posted via http://www.ruby-forum.com/.