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