To translate this query to railsway

Hi
   I have the tables

users, roles, memberships( join table between users and roles. Between
users and roles there is has_many :through relation ship)

privileges, permissions (join table between roles and privileges.
Between roles and privileges there is has_many :through relation ship)

privileges table entries like
1 add_user
2 delete_user ,.....

roles tables entries like
1 admin
2 participant,.....

     Now to find whether a given user(with id=2) has a privilege like
'add_user' I wrote a query like

select memberships.id from memberships inner join permissions on
memberships.role_id=permissions.role_id where memberships.user_id=2 and
permissions.privilege_id=(select id from privileges where privilege_name
='add_user');

       And I get the correct result. Now I would like to know how I can
write the above query in an activerecord style.Please help

Thanks in advance
Tom

Tom Mac wrote:

Hi
   I have the tables

users, roles, memberships( join table between users and roles. Between
users and roles there is has_many :through relation ship)

privileges, permissions (join table between roles and privileges.
Between roles and privileges there is has_many :through relation ship)

privileges table entries like
1 add_user
2 delete_user ,.....

roles tables entries like
1 admin
2 participant,.....

     Now to find whether a given user(with id=2) has a privilege like
'add_user' I wrote a query like

select memberships.id from memberships inner join permissions on
memberships.role_id=permissions.role_id where memberships.user_id=2 and
permissions.privilege_id=(select id from privileges where privilege_name
='add_user');

       And I get the correct result. Now I would like to know how I can
write the above query in an activerecord style.Please help

Thanks in advance
Tom

Hi Tom,

As I understand u have set such kind of relations

User
has_one :membership
has_one :role, :through => :membership

Role
has_many :memberships
has_many :users, :through => :memberships
has_many :permissions
has_many :privileges, :through => :permissions

Membership
belongs_to :user
belongs_to :role

Privilege
has_many :permissions
has_many :roles, :through => :permissions

Permission
belongs_to :privilege
belongs_to :role

And to find whether user with id=2 has privilege like 'add_user' you can
use following query.

User.find(2).role.privileges.include?('add_user')

Hi Hitesh

User
has_one :membership
has_one :role, :through => :membership

Role
has_many :memberships
has_many :users, :through => :memberships
has_many :permissions
has_many :privileges, :through => :permissions

Membership
belongs_to :user
belongs_to :role

Privilege
has_many :permissions
has_many :roles, :through => :permissions

Permission
belongs_to :privilege
belongs_to :role

   Thanks for your reply. All otheres except User are right. User is

User
has_many :memberships
has_many :role, :through => :memberships

Thanks
Tom

Then you can try this

User.find(2).roles.collect({|role|
role.privileges.include?('add_user')})