ActsAsNestedSet's all_children associations

rubyonrails-talk
1

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

2

Sam Morrison wrote:

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

@documents = Documents.find :all, :joins => <<-END    join categories on categories.id = documents.category_id    and categories.lft between #{category.lft} and #{category.rgt} END

3

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.

4

Sam Morrison wrote:

5

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?

6

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...

7

Sam Morrison 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

8

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

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