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/teac... 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!
on 2006-05-12 12:57
on 2006-05-12 18:41
On May 12, 2006, at 3:57 AM, Greg Newman wrote: > > 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
on 2006-05-13 04:09
> 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.