# Has\_many association based on common target rather than join

**URL:** <https://www.ruby-forum.com/t/has-many-association-based-on-common-target-rather-than-join/105095>\
**Category:** Rails\
**Created:** [May 25, 2007, 7:07pm UTC](https://www.ruby-forum.com/t/has-many-association-based-on-common-target-rather-than-join/105095 "2007-05-25T19:07:23Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 25, 2007, 7:07pm UTC](https://www.ruby-forum.com/t/has-many-association-based-on-common-target-rather-than-join/105095/1 "2007-05-25T19:07:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 25, 2007, 7:40pm UTC](https://www.ruby-forum.com/t/has-many-association-based-on-common-target-rather-than-join/105095/2 "2007-05-25T19:40:25Z")

</div>

All users with @permission should be:

@permission.group.users

Unless I’m missing something.

On 5/25/07, removed\_email\_address@domain.invalid [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> t.column “staff”, :boolean  
> has\_many :users  
> permission X"?
> 
> It’s ugly, and it almost works. Problem is, the “id” field of the

–  
Collin M.  
641 451 0380

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 25, 2007, 8:02pm UTC](https://www.ruby-forum.com/t/has-many-association-based-on-common-target-rather-than-join/105095/3 "2007-05-25T20:02:27Z")

</div>

On May 25, 1:39 pm, “Collin M.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> 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

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [May 25, 2007, 8:37pm UTC](https://www.ruby-forum.com/t/has-many-association-based-on-common-target-rather-than-join/105095/4 "2007-05-25T20:37:33Z")

</div>

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

On 5/25/07, removed\_email\_address@domain.invalid [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Yes, this is correct. But what I’m seeking is all users with  
> -charlie
> 
> >

–  
Collin M.  
641 451 0380