AR inner join with foreign key cannot get it right

Rails
1

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 ?

2

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

3

On Wed, Apr 6, 2011 at 8:43 AM, Erwin [email protected] wrote:

has_many :users, :through => :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.

4

On 6 April 2011 14:43, Erwin [email protected] 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