I have the following models: <code> 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 </code>
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: <code> has_many :permissions, :through => :group </code> 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": <code> # 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]) </code> 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.*": <code> 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]) </code>
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
