# ActsAsNestedSet's all\_children associations **URL:** \ **Category:** Rails\ **Created:** [August 23, 2007, 11:11am UTC](https://www.ruby-forum.com/t/actsasnestedsets-all-children-associations/112794 "2007-08-23T11:11:00Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![veganopolis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/ecae2f/32.png) [@veganopolis](https://www.ruby-forum.com/u/veganopolis)\ **Post date:** [August 23, 2007, 11:11am UTC](https://www.ruby-forum.com/t/actsasnestedsets-all-children-associations/112794/1 "2007-08-23T11:11:00Z")
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 ---
**Author:** ![veganopolis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/ecae2f/32.png) [@veganopolis](https://www.ruby-forum.com/u/veganopolis)\ **Post date:** [August 23, 2007, 3:42pm UTC](https://www.ruby-forum.com/t/actsasnestedsets-all-children-associations/112794/2 "2007-08-23T15:42:41Z")
Sam M. wrote: > subcategories.each do |subcategories| > @documents += subcategory.documents > end @documents = Documents.find :all, :joins =\> \<\<-END join categories on categories.id = documents.category\_id and categories.lft between #{category.lft} and #{category.rgt} END – We develop, watch us RoR, in numbers too big to ignore. ---
**Author:** ![veganopolis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/ecae2f/32.png) [@veganopolis](https://www.ruby-forum.com/u/veganopolis)\ **Post date:** [August 23, 2007, 4:17pm UTC](https://www.ruby-forum.com/t/actsasnestedsets-all-children-associations/112794/3 "2007-08-23T16:17:49Z")
On 8/23/07, Mark Reginald J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > @documents = Documents.find :all, :joins =\> \<\<-END > join categories on categories.id = documents.category\_id > and categories.lft between #{category.lft} and #{category.rgt} > END Thanks Mark, but that is actually similar to what I am already doing. I mentioned in my original post that I wasn’t using subcategories.each approach and had already re-coded this as a join. Thanks for taking the time to respond. I do appreciate it. I will stick with what I am using until I can find a better solution. – sam ---
**Author:** ![veganopolis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/ecae2f/32.png) [@veganopolis](https://www.ruby-forum.com/u/veganopolis)\ **Post date:** [August 24, 2007, 1:46am UTC](https://www.ruby-forum.com/t/actsasnestedsets-all-children-associations/112794/4 "2007-08-24T01:46:21Z")
On 8/23/07, Mark Reginald J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Sam, the above is only one query, while the code in your post uses > three, plus some Ruby work. Mark, thanks again. I guess I should have re-worded my post a bit. I apologize for not being more clear about what I am looking for. I am not looking for a “more efficient” query. In my situation I am using an join table to link documents to categories. What I am looking for is something more “railsish”, something along the lines of: subcategories = category.all\_children documents = subcategories.documents or even documents = category.all\_children.documents I know this particular syntax doesn’t work, but is anything similar to this possible? – sam ---
**Author:** ![veganopolis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/ecae2f/32.png) [@veganopolis](https://www.ruby-forum.com/u/veganopolis)\ **Post date:** [August 23, 2007, 4:39pm UTC](https://www.ruby-forum.com/t/actsasnestedsets-all-children-associations/112794/5 "2007-08-23T16:39:51Z")
Sam M. wrote: > Thanks for taking the time to respond. I do appreciate it. I will > stick with what I am using until I can find a better solution. Sam, the above is only one query, while the code in your post uses three, plus some Ruby work. If you really want to get rid of the explicit join you can rewrite as: @documents = Document.find :all, :include =\> :category, :conditions =\> “categories.lft between #{category.lft} and #{category.rgt}” – We develop, watch us RoR, in numbers too big to ignore. ---
**Author:** ![veganopolis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/ecae2f/32.png) [@veganopolis](https://www.ruby-forum.com/u/veganopolis)\ **Post date:** [August 24, 2007, 4:10am UTC](https://www.ruby-forum.com/t/actsasnestedsets-all-children-associations/112794/6 "2007-08-24T04:10:32Z")
On 8/24/07, Sam M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > On 8/23/07, Mark Reginald J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > Sam, the above is only one query, while the code in your post uses > > three, plus some Ruby work. Mark, I decided to refactor my app a bit. I was able to move some of the relationship specific attributes to either of the document / category models and even get rid of a few things I no longer needed. This allowed me to simplify the relationship and remove the join table all together. Your example is a much more efficient approach than what I was originally doing. I would still like to be able to do something like this though: documents = category.all\_children.documents though, I am assuming this isn’t possible. Either way. thanks again for your post, I was unaware of the :includes parameter. The SQL developer side of me makes me want to just dig in and code these queries myself. I am just trying to find the most efficient, railsish, way of doing things so I am stay true to the framework. Again, I appreciate your responses and I learned something new… – sam ---
**Author:** ![veganopolis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/ecae2f/32.png) [@veganopolis](https://www.ruby-forum.com/u/veganopolis)\ **Post date:** [August 24, 2007, 11:50am UTC](https://www.ruby-forum.com/t/actsasnestedsets-all-children-associations/112794/7 "2007-08-24T11:50:20Z")
Sam M. wrote: > originally doing. I would still like to be able to do something like > this though: > > documents = category.all\_children.documents Why don’t you just hide the Document find in a Category instance method: class Category def all\_documents Document.find :all, :joins =\> \<\<-END join categories on categories.id = documents.category\_id and categories.lft between #{lft} and #{rgt} END end end allowing you to write ``` documents = category.all_documents ``` > though, I am assuming this isn’t possible. Either way. thanks again > for your post, I was unaware of the :includes parameter. The SQL > developer side of me makes me want to just dig in and code these > queries myself. I am just trying to find the most efficient, railsish, > way of doing things so I am stay true to the framework. Use of :include =\> :category sacrifices efficiency for simplicity because it fetches, instantiates, and attaches a Category model to each document, which you probably don’t need. However I’ve recently released a plugin which allows you to get the join without any data by writing :include =\> :category[] class Category def all\_documents Document.find :all, :include =\> :category[], :conditions =\> {:lft =\> lft…rgt} end end – We develop, watch us RoR, in numbers too big to ignore. ---
**Author:** ![veganopolis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/v/ecae2f/32.png) [@veganopolis](https://www.ruby-forum.com/u/veganopolis)\ **Post date:** [August 24, 2007, 1:33pm UTC](https://www.ruby-forum.com/t/actsasnestedsets-all-children-associations/112794/8 "2007-08-24T13:33:40Z")
On 8/24/07, Mark Reginald J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > end > Mark, this works well because I access a category’s documents from > several locations, so this allows me to keep things DRY and concise. > Thanks for reminding me about the instance method approach. I guess I > was just thinking that this situation would be more common… > However I’ve recently released a plugin which allows you to get the join without > any data by writing :include =\> :category[] Where is this? I would like to take a look. Thanks again, sam