I have the following models:
create table “users” do |t|
t.column “name”, :string
t.column “group_id”, :integer
end
create table “groups” do
t.column “name”, :string
t.column “staff”, :boolean
end
create table “permissions” do
t.column “group_id”, :integer
t.column “permission”, :string
end
class User < ActiveRecord::Base
belongs_to :group
end
class Group < ActiveRecord::Base
has_many :users
has_many :permissions
end
class Permission < ActiveRecord::Base
belongs_to :group
end
The question: How do I specify the association between Users and
Permissions, so that I can do things like user.permissions? In
particular, how do I set things up so I can ask “get all Users with
permission X”?
First, I tried adding this to User:
has_many :permissions, :through => :group
This doesn’t work; I get errors stating that the groups table does not
have a group_id field. I guess this makes sense if the “through”
target is supposed to be something that User “has_many” of instead of
“belongs_to”. For “through”, Group should be a join table between
User and Permission, rather than just a common target between the two.
Giving up on associations, I tried setting up find queries with custom
joins. I can’t use “:include => :permissions”, as there’s no
association there, so I have to do it with explicit “:joins”:
find all Users with “foo” permission
User.find(:all, :joins => “INNER JOIN groups ON users.group_id =
groups.id
INNER JOIN permissions ON groups.id = permissions.group_id”,
:conditions => [“permissions.permission = ?”, foo])
It’s ugly, and it almost works. Problem is, the “id” field of the
returned User objects is overwritten by “id” fields of subsequent join
objects. To prevent this, I need to either specify the order of
joins, or limit the returned fields to “users.":
User.find(:all, :joins => “INNER JOIN groups ON users.group_id =
groups.id
INNER JOIN permissions ON groups.id = permissions.group_id”,
:select => "users.
:conditions => [“permissions.permission = ?”, foo])
This seems to have gotten absurdly complicated, given the seemingly
simple model structure, and is in fact more verbose and possibly less
clear than a straight find_by_sql query. Is there a better way to
cleanly specify this association to avoid so much SQL?
cheers,
Charlie