How do I find records based on attributes of a many-to-many-related model?

Question

Models... ``` InternalUser has_many :internal_user_roles has_many :roles, :through => :internal_user_roles InternalUserRole belongs_to :internal_user belongs_to :role Role has_many :internal_user_roles has_many :internal_users, :through => :internal_user_roles ``` Using the new ActiveRecord query API, how would I find all the `InternalUser`s with the "ADMIN" role? In other words, how do I generate this query... ``` SELECT * FROM internal_users i, internal_user_roles ir, roles r WHERE i.id = ir.internal_user_id AND r.id = ir.internal_user_id AND r.name = 'ADMIN' ```

Accepted Answer

Ideally you should start with an object that was the admin role:

```
role = Role.find_by_name('ADMIN')
```

Then you could simply query the internal users for that role:

```
role.internal_users
```

If you want to go further, you should note that all associations in ActiveRecord can be further queried upon:

```
role.internal_users.where(:first_name => 'Bill').limit(5)
```

Getting back to the original question, alternatively you could query the InternalUser model:

```
InternalUser.includes(:roles).where(['roles.id = ?', role])
```

or perhaps a bit faster, but more complex code-wise:

```
InternalUser.includes(:internal_user_roles).where(['internal_user_roles.role_id = ?', role])
```

To translate your SQL query directly, you could also do this:

```
InternalUser.includes(:roles).where("roles.name = 'ADMIN'")
```

You can also see the SQL that ActiveRecord would generate (for any of these queries) by putting a 'to_sql' call at the end, like so:

```
InternalUser.includes(:roles).where("roles.name = 'ADMIN'").to_sql
```

How do I find records based on attributes of a many-to-many-related model?

Solution

Problem