So you’ve created a table of groups that are in a hierarchy with the gem ancestry, and you’ve created a table of users, and you’ve joined users to groups with a join table.
And now you want to use Thinking Sphinx, which is awesome for searching. So you think, hey, wouldn’t it be great if I could do a search for all users in a group’s subtree? Except that Thinking Sphinx cares not for your puny ancestry methods, and :subtree_ids doesn’t work in an index.
Fear not! I’ve done the heavy lifting for you.
in user.rb:
define_index do
indexes last_name
has groups(:id), :as => :direct_group_ids
has "CONCAT_WS('/',groups.id,groups.ancestry)", :as => :group_ids, :type => :multi
end
The first attribute is so that the join is made from users to groups. The second actually creates the multi-value attribute that you can search on. So from there you can do:
User.search(:with_all => {:group_ids => [1]})
That will give you all the users that belong to the subtree, including the root group (in this case, the group with the id of “1″).
That being said, if you only want the users from sub-groups of the group you’re searching on (i.e. you never want users that are directly attached to the group you’re searching on), you can instead do this:
in user.rb:
define_index do
indexes last_name
has groups(:ancestry), :as => :group_ids, :type => :multi
end
So if you have group 1 which has group 2 and group 3 as children, the first example will give you all the users attached to all 3 groups; the second example will only give you the users attached to groups 2 and 3.
One last gotcha: if you’re running the search in console, remember to add “:per_page => 100″ or however many entries you want back, or else by default you only get 20. Don’t want you to headdesk when you can’t figure out why it’s returning 20 users when it’s supposed to be returning 75.
Happy indexing!