CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3activerecordarel
Ethan
Ethan

Reputation: 60099

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

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

Upvotes: 3

Views: 10939

Answers (1)

Brian Underwood
Brian Underwood

Reputation: 10856

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

Upvotes: 8

Related Questions