Rails - May 2007 - 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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

All users with @permission should be:

@permission.group.users

Unless I''m missing something.

On 5/25/07, chazen-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
<chazen-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
>
> 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
>
>
> >
>

-- 
Collin Miller
641 451 0380

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On May 25, 1:39 pm, "Collin Miller"
<collintmil...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On 5/25/07, chazen-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
<chazen-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
>
> On May 25, 1:39 pm, "Collin Miller"
<collintmil...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
>
>
> >
>

-- 
Collin Miller
641 451 0380

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---