AR inner join with foreign key .. cannot get it right ...

rubyonrails-talk
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

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

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