Has many through join table issues

Rails
1

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!

2

On May 12, 2006, at 3:57 AM, Greg N. 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

3

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.