# To translate this query to railsway

**URL:** <https://www.ruby-forum.com/t/to-translate-this-query-to-railsway/185992>\
**Category:** Rails\
**Created:** [March 30, 2010, 7:20am UTC](https://www.ruby-forum.com/t/to-translate-this-query-to-railsway/185992 "2010-03-30T07:20:38Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![tom-mac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/bc8723/32.png) [@tom-mac](https://www.ruby-forum.com/u/tom-mac)\
**Post date:** [March 30, 2010, 7:20am UTC](https://www.ruby-forum.com/t/to-translate-this-query-to-railsway/185992/1 "2010-03-30T07:20:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tom-mac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/bc8723/32.png) [@tom-mac](https://www.ruby-forum.com/u/tom-mac)\
**Post date:** [March 30, 2010, 2:11pm UTC](https://www.ruby-forum.com/t/to-translate-this-query-to-railsway/185992/2 "2010-03-30T14:11:24Z")

</div>

Tom M. 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’)

---

<div class="post-metadata">

**Author:** ![tom-mac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/bc8723/32.png) [@tom-mac](https://www.ruby-forum.com/u/tom-mac)\
**Post date:** [March 30, 2010, 2:19pm UTC](https://www.ruby-forum.com/t/to-translate-this-query-to-railsway/185992/3 "2010-03-30T14:19:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![tom-mac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/bc8723/32.png) [@tom-mac](https://www.ruby-forum.com/u/tom-mac)\
**Post date:** [March 30, 2010, 2:49pm UTC](https://www.ruby-forum.com/t/to-translate-this-query-to-railsway/185992/4 "2010-03-30T14:49:43Z")

</div>

Then you can try this

User.find(2).roles.collect({|role|  
role.privileges.include?(‘add\_user’)})