ActsAsNestedSet's all_children associations

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

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.

On 8/23/07, Mark Reginald J. [email protected] 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

On 8/23/07, Mark Reginald J. [email protected] 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

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.

On 8/24/07, Sam M. [email protected] wrote:

On 8/23/07, Mark Reginald J. [email protected] 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

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.

On 8/24/07, Mark Reginald J. [email protected] 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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs