In Ruby, with ActiveRecord what is the best way to retrieve an object that belongs_to a parent object, given some conditions?

Question

In my model an Organisation has_many :users, and a User has_and_belongs_to_many :roles and a Role has a name and has_and_belongs_to_many :users.

In my Organisation class I have a method get_admin that is supposed to get the User belonging to that Organisation who has the Role 'admin'. Like so:

def get_admin
  return self.users.with_role('admin')
end

Alas this returns an ActiveRecord::Relation object, not a User.

I tried appending .first to the end of the line like so

def get_admin
  return self.users.with_role('admin').first
end

But then all I get is an SQL error

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: role.name: SELECT  "users".* FROM "users" WHERE ("users".organisation_id = 2) AND (role.name == 'admin') LIMIT 1

My schema is defined thusly:

create_table "roles", :force => true do |t|
  t.string "name", :null => false
end

create_table "users", :force => true do |t|
  t.string  "username",                              :null => false
  t.integer "organisation_id"
end

create_table "roles_users", :id => false, :force => true do |t|
  t.integer "user_id"
  t.integer "role_id"
end

create_table "organisations", :force => true do |t|
  t.string  "name",                                    :null => false
  t.string  "website",                                 :null => false
end

How would I rewrite the Organisation's get_admin method (as follows) to return an actual User?

def get_admin
  return self.users.with_role('admin')
end

Cheers

Dave

Answer 1

Create a scope called admin in Users model

user.rb:

scope :admin, joins(:role).where('roles.name = ?', 'admin')

And the get_admin method should be

def get_admin
  return self.users.admin.first
end