Rails - Apr 2011 - AR inner join with foreign key .. cannot get it right ...

I have a simple structure , but I have to use existing primary keys
from external db, I wrote :

class User < ActiveRecord::Base   # ( uid primary key )
  has_one :users_role, :foreign_key => :uid
  has_one :role, :through => :users_role
end

class Role < ActiveRecord::Base    #  (  rid primary key)
  has_many   :users_role, :foreign_key => :rid
  has_many   :users, :through => :users_role
end

class UsersRole < ActiveRecord::Base   #  (  uid / rid fields in the
table  and  I should not have id key there)
  belongs_to :role, :primary_key => :rid
  belongs_to  :user, :primary_key => :uid
end

I can get :
Role.first.users_role
BUT
Role.first.users    raises an error  ( using users.id .... and
users_id  keys ..)
ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column
''users.id'' in ''on clause'': SELECT `users`.*
FROM `users` INNER JOIN
`users_roles` ON `users`.id = `users_roles`.user_id WHERE
((`users_roles`.rid = NULL))

what''s wrong in my writing ?

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Is there any reason why you do not use the standard FK names so you do not 
have to tell it what they are?

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Wed, Apr 6, 2011 at 8:43 AM, Erwin
<yves_dufour-ee4meeAH724@public.gmane.org> wrote:
> I have a simple structure , but I have to use existing primary keys
> from external db, I wrote :
>
> class User < ActiveRecord::Base   # ( uid primary key )
>  has_one :users_role, :foreign_key => :uid
>  has_one :role, :through => :users_role
> end
>
> class Role < ActiveRecord::Base    #  (  rid primary key)
>  has_many   :users_role, :foreign_key => :rid
>  has_many   :users, :through => :users_role
> end
>
> class UsersRole < ActiveRecord::Base   #  (  uid / rid fields in the
> table  and  I should not have id key there)
>  belongs_to :role, :primary_key => :rid
>  belongs_to  :user, :primary_key => :uid
> end
>
> I can get :
> Role.first.users_role
> BUT
> Role.first.users    raises an error  ( using users.id .... and
> users_id  keys ..)
> ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column
> ''users.id'' in ''on clause'': SELECT
`users`.* FROM `users` INNER JOIN
> `users_roles` ON `users`.id = `users_roles`.user_id WHERE
> ((`users_roles`.rid = NULL))
>
> what''s wrong in my writing ?
>
You''re missing a sorce on your has_many   :users, :through =>
:users_role in
model Role. It''s trying to use :role in model UserRole because that is
what
the has_many   :users_role, :foreign_key => :rid from model Role is set to
use. Do the following:


Change this:

:users, :through => :users_role

To this:

:users, :through => :users_role, :source => :user

That should fix your issue. Let us know how it goes.

B.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 6 April 2011 14:43, Erwin <yves_dufour-ee4meeAH724@public.gmane.org>
wrote:
> I have a simple structure , but I have to use existing primary keys
> from external db, I wrote :
>
> class User < ActiveRecord::Base   # ( uid primary key )
You need to use set_primary_key here to tell rails that the primary
key of the users_table is uid
>  has_one :users_role, :foreign_key => :uid
>  has_one :role, :through => :users_role
> end
>
> class Role < ActiveRecord::Base    #  (  rid primary key)
Again you need set_primary_key
>  has_many   :users_role, :foreign_key => :rid
>  has_many   :users, :through => :users_role
> end
>
> class UsersRole < ActiveRecord::Base   #  (  uid / rid fields in the
> table  and  I should not have id key there)
>  belongs_to :role, :primary_key => :rid
>  belongs_to  :user, :primary_key => :uid
Those should be foreign_key I think.  I am not sure how you tell rails
that there is no primary key for this table.
> end
>
> I can get :
> Role.first.users_role
> BUT
> Role.first.users    raises an error  ( using users.id .... and
> users_id  keys ..)
> ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column
> ''users.id'' in ''on clause'': SELECT
`users`.* FROM `users` INNER JOIN
The error is because it thinks users has an id field, as you did not
set_primary_key

Colin

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.