To translate this query to railsway

rubyonrails-talk
1

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

2

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')

3

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

4

Then you can try this

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