Hello, I am wondering if there is a better way to implement this.
I have to models, a Document model and a Category model. The Category model is self-referencing through acts_as_nested_set.
category: id title parent_id lft rgt
documents: id details...
categories_documents: category_id document_id
I have my app set up in a way that the Documents controller has a list view that includes a Category filter utility. This allows the user to see all documents associated with a selected category and all descendant categories, (using all_children, not direct_children). The problem I found was that there wasn't a clean way to return all of the associated documents for all_children without running a new SELECT for each child. Something like this:
@documents = category.documents subcategories = category.all_children subcategories.each do |subcategories| @documents += subcategory.documents end
This runs a separate query for each of the subcategories to find each subcategory's documents. There are about ten thousand categories so this isn't an option in my situation.
I have changed it to something like this which is much faster / efficient:
@categories = category.documents # turn all subcategory ids into a string subcategories = category.all_children.map {|subcategory| subcategory.id}.join(", ")
and then use subcategories as part of my Document look up:
@documents += Document.find(:all, :conditions => "d.category_id IN (#{subcategories})", :joins => "AS d JOIN categories_documents AS cd ON d.id = cd.planner_id")
This works great and allows me to get all of the documents in one query. So my question, is there a better approach to this problem? Something that allows me to avoid defining the join? If not, no big deal, I am just trying to see if I missed something here.
Thanks for taking time to read this.
Sam