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