Looking for a simpler model using :through associations

Hi. I’m building a setup where I have users and groups, and a user can
have an optional title in a group (eg. “owner”). I’m doing this using
“has_many :through” associations, using a Role model object as the join
hub:

class User < ActiveRecord::Base
has_many :roles, :dependent => :destroy
has_many :groups, :through => :roles
end

class Group < ActiveRecord::Base
has_many :roles, :dependent => :destroy
has_many :users, :through => :roles
end

#The roles table has columns (id, user_id, group_id, title_id)
class Role < ActiveRecord::Base
:belongs_to :user
:belongs_to :group
:has_one :title
end

class Title < ActiveRecord::Base
:belongs_to :role
end

A typical use case is to find the owner of a given group, or more
generally, find users with a given title in a given group. This can be
done roughly like:

class Group < ActiveRecord::Base
has_many :roles, :dependent => :destroy
has_many :users, :through => :roles

def find_users_in_group_with_title(title)
User.find_by_sql([
"SELECT users.* from users, roles, titles, groups "+
"WHERE users.id = roles.user_id "+
"AND groups.id = roles.group_id "+
"AND titles.id = roles.title_id "+
"AND titles.name = ? "+
“AND groups.id = ?”], title, self.id)
end
end

I find this (esp. the query) rather clumsy, as the core of the problem
is simply “a user can have a role in a given group”. Has anyone come up
with a more elegant model for expressing this?

Br,

Morten