I''m trying to wrap my caffeine soaked brain around has_many :through following along at: http://rails.techno-weenie.net/tip/2005/12/23/teaching_your_blog_model_new_tricks_with_has_many_through I think my models are a little more complex than what fits this narrative. I have a directory of members, each member can belong to multiple categories. The category table references itself to build a category list, joined with parent_id. Top level categories will not have members, only subcategories. ie. Finance is top level (no member associated). Accountants, financial advisors, investors will be subcategories (with lots of members). I''m using a join table between members and categories to build the associations between members and the category. class MemberToCategory < ActiveRecord::Base belongs_to :members belongs_to :categories end class MemberType < ActiveRecord::Base has_many :member_to_categories has_many :members, :through => :member_to_categories class Member < ActiveRecord::Base has_many :member_to_categories has_many :member_types, :through => :member_to_categories My problem is two-fold. 1. When the visitor selects a top level category, I need to pull all members of all of the related subcategories. 2. When a subcategory is selected, I only need to pull the members who belong to that subcategory only. My Category table: id name parent_id my member_to_categories table: member_id category_id Any help will be greatly appreciated! -- Posted via http://www.ruby-forum.com/.
On May 12, 2006, at 3:57 AM, Greg Newman wrote:> I''m trying to wrap my caffeine soaked brain around has_many :through > following along at: > http://rails.techno-weenie.net/tip/2005/12/23/ > teaching_your_blog_model_new_tricks_with_has_many_through > > I think my models are a little more complex than what fits this > narrative. > > I have a directory of members, each member can belong to multiple > categories. > > The category table references itself to build a category list, joined > with parent_id. Top level categories will not have members, only > subcategories. > ie. Finance is top level (no member associated). Accountants, > financial > advisors, investors will be subcategories (with lots of members).This adjacency list requires recursive queries to determine root category or all subcategories so a single-query :through association is not possible. Consider using a nested set representation for your hierarchy (see acts_as_nested_set) since it allows you to select all subcategories in one query by checking the ''span'' of its set. Then you may: class Categorization < AR::Base belongs_to :category belongs_to :member end class Member < AR::Base has_many :categorizations has_many :categories, :through => :categorizations end # categories: id, parent_id, lft, rgt class Category < AR::Base acts_as_nested_set has_many :categorizations has_many :direct_members, :through => :categorizations, :source => :member def members Member.find :all, :select => ''distinct members.*'', :conditions => [''c.lft between ? and ?'', lft, rgt], :join => '' join categorizations cm on cm.member_id = members.id join categories c on cm.category_id = c.id'' end end Best, jeremy
> Consider using a nested set representation for your hierarchy (see > acts_as_nested_set) since it allows you to select all subcategories > in one query by checking the ''span'' of its set. >I had considered that Jeremy. I didn''t want to go that route since updating the categories requires a table rewrite. Some category tables could contain alot of rows. The other problem is that this project is a rewrite of an existing directory engine, so updating the category table to include the lft, rgt, level could be a pain. I''ve got a good view for the categories. My real problem is in displaying the members for the categories. I suppose I could use a find_by_sql to get the data. -- Posted via http://www.ruby-forum.com/.