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