Forum: Ruby on Rails Looking for a simpler model using :through associations

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
350b7c39d2973b5ae483c65729a5bfcc?d=identicon&s=25 Morten (Guest)
on 2006-04-12 19:06
(Received via mailing list)
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
This topic is locked and can not be replied to.