Forum: Ruby on Rails Resolving ambiguous columns during a join

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.
Duane (Guest)
on 2005-12-29 22:05
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!
Damon C. (Guest)
on 2005-12-30 01:45
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/
This topic is locked and can not be replied to.