Forum: Ruby on Rails Has many through join table issues

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.
Greg N. (Guest)
on 2006-05-12 14:57
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!
Jeremy K. (Guest)
on 2006-05-12 20:41
(Received via mailing list)
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
Greg N. (Guest)
on 2006-05-13 06: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.
This topic is locked and can not be replied to.