Rails - Aug 2007 - 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
We develop, watch us RoR, in numbers too big to ignore.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 8/23/07, Mark Reginald James
<mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org>
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Sam Morrison wrote:
> On 8/23/07, Mark Reginald James
<mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> 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, 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.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 8/23/07, Mark Reginald James
<mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org>
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 8/24/07, Sam Morrison <sam-o6v14nQ79ppXqviUI+FSNg@public.gmane.org>
wrote:
> On 8/23/07, Mark Reginald James
<mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org> 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

-- 
We develop, watch us RoR, in numbers too big to ignore.


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On 8/24/07, Mark Reginald James
<mrj-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org>
wrote:
> 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---