has_many association based on common target rather than join table?

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

All users with @permission should be:

@permission.group.users

Unless I’m missing something.

Yes, this is correct. But what I'm seeking is all users with
@permission.permission == "foo". There could be many entries (many
@permission's) with the same string in the "permission" column.

I could change the models so that the permission table does not
contain a group_id, and to instead use a "has_and_belongs_to_many"
join between the Group and Permission tables. I might then be able to
get "has_many :through" to work between permissions and users...

thanks for poking me in a different direction, it might work out.

-charlie

Yes, I would do the many-to-many groups/permissions in this situation. Good luck.