# Unnecessary queries

**URL:** <https://www.ruby-forum.com/t/unnecessary-queries/174153>\
**Category:** Rails\
**Created:** [August 21, 2009, 12:20pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153 "2009-08-21T12:20:39Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![jhaagmans](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc8723/32.png) [@jhaagmans](https://www.ruby-forum.com/u/jhaagmans)\
**Post date:** [August 21, 2009, 12:20pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153/1 "2009-08-21T12:20:39Z")

</div>

Hi,

I have managed to create a table that includes many nested sets. I  
have a problem with it though. I can retrieve one set by getting its  
root element and then do something like:

@attributes = root\_attribute.self\_and\_descendants

However, when I put the following in my views to be able to descend  
into the model:

\<% @attributes.root.children.each do |attribute| %\>  
…  
\<% end %\>

It will generate a new query for the root element as well as every  
child it handles. That’s not what I want. The reason why I was told to  
use “awesome nested set” is because I can retrieve my entire nested  
set in one single query and proceed from there. I have certain sets  
that will generate over 100 queries this way, that’s not pretty!

Can anyone tell me what I’m doing wrong?

---

<div class="post-metadata">

**Author:** ![jhaagmans](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc8723/32.png) [@jhaagmans](https://www.ruby-forum.com/u/jhaagmans)\
**Post date:** [August 21, 2009, 12:27pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153/2 "2009-08-21T12:27:41Z")

</div>

Am 21.08.2009 um 12:19 schrieb jhaagmans:

> It will generate a new query for the root element as well as every  
> child it handles. That’s not what I want. The reason why I was told to  
> use “awesome nested set” is because I can retrieve my entire nested  
> set in one single query and proceed from there. I have certain sets  
> that will generate over 100 queries this way, that’s not pretty!
> 
> Can anyone tell me what I’m doing wrong?

Try using the view helper as described in the README

> **[GitHub - collectiveidea/awesome\_nested\_set: An awesome replacement for...](https://github.com/collectiveidea/awesome_nested_set/tree/master)**
>
> master

Mit freundlichen  
Grüßen,  
Felix Schäfer

---

<div class="post-metadata">

**Author:** ![jhaagmans](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc8723/32.png) [@jhaagmans](https://www.ruby-forum.com/u/jhaagmans)\
**Post date:** [August 21, 2009, 1:48pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153/3 "2009-08-21T13:48:28Z")

</div>

Am 21.08.2009 um 12:19 schrieb jhaagmans:

> @attributes = root\_attribute.self\_and\_descendants
> 
> However, when I put the following in my views to be able to descend  
> into the model:
> 
> \<% @attributes.root.children.each do |attribute| %\>  
> …  
> \<% end %\>

I think I now see where the problem is. To be quite honest, I’m  
surprised this even works, because @attributes is a set, and you  
shouldn’t be able to call root on it. Anyway, self\_and\_descendants  
gets you a flat set, from which you could reconstruct a tree on your  
own, but I don’t think that would be that easy.

What I can come up right now is a way to cut the calls down to the  
number of nodes with children with something like:

“”"  
def my\_awesome\_helper(root)  
result = “

” + “- ” + root.someattribute  
if root.children {  
root.children.each do |child|  
result += my\_awesome\_helper(child)  
end  
end  
result += “
” + “
”  
end  
“”"  
(yeah, ugly, and I’m not even sure the syntax is right, but you get  
the idea)

You’d then just have to call the helper on the root node in your view.

Mit freundlichen  
Grüßen,  
Felix Schäfer

---

<div class="post-metadata">

**Author:** ![jhaagmans](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc8723/32.png) [@jhaagmans](https://www.ruby-forum.com/u/jhaagmans)\
**Post date:** [August 21, 2009, 2:07pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153/4 "2009-08-21T14:07:47Z")

</div>

On 21 aug, 13:16, Felix Schäfer [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> def my\_awesome\_helper(root)  
> the idea)
> 
> You’d then just have to call the helper on the root node in your view.
> 
> Mit freundlichen Grüßen,
> 
> Felix Schäfer

Danke Felix!

I’m quite unsure whether this would limit the number of queries. I  
already call children.each in my views, so this would probably, in  
terms of performance, won’t really help me. I was told I could perform  
this using one single query, but that seems impossible. And that  
brings me back to a point where I question the advantages of using  
awesome\_nested\_set over using acts\_as\_tree in combination with  
acts\_as\_list.

The thing is, it all made sense! And it would have been perfect.

---

<div class="post-metadata">

**Author:** ![jhaagmans](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc8723/32.png) [@jhaagmans](https://www.ruby-forum.com/u/jhaagmans)\
**Post date:** [August 21, 2009, 12:38pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153/5 "2009-08-21T12:38:49Z")

</div>

On 21 aug, 12:26, Felix Schäfer [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Try using the view helper as described in the READMEhttp://github.com/collectiveidea/awesome\_nested\_set/tree/master
> 
> Mit freundlichen Grüßen,
> 
> Felix Schäfer

Danke Felix!

The thing is: I don’t understand it. The rdoc is a bit more extensive  
on this and I think I get the syntax of nested\_set\_options, but I  
don’t understand how to really use this in my view. Should I still  
send the self\_and\_descendants object from my controller and how can I  
use this to generate lists within lists (

 within - )? The usage  
with ‘-’ \* i.level is very ugly and totally unsuitable in my case.

I want a parent to put an

 around its children and every child  
within an - tag. But the nested\_set\_options method doesn’t seem to  
be able to work this way.

---

<div class="post-metadata">

**Author:** ![jhaagmans](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc8723/32.png) [@jhaagmans](https://www.ruby-forum.com/u/jhaagmans)\
**Post date:** [August 21, 2009, 2:58pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153/6 "2009-08-21T14:58:08Z")

</div>

On 21 aug, 07:32, jhaagmans [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> > > However, when I put the following in my views to be able to descend  
> > > into the model:
> 
> > > \<% @attributes.root.children.each do |attribute| %\>  
> > > …  
> > > \<% end %\>
> 
> > I think I now see where the problem is. To be quite honest, I’m  
> > surprised this even works, because @attributes is a set, and you  
> > shouldn’t be able to call root on it.

Yeah, that shouldn’t work at all. According to the rdoc, Model#root  
should only be defined as an instance method on Model objects.

> > Anyway, self\_and\_descendants  
> > gets you a flat set, from which you could reconstruct a tree on your  
> > own, but I don’t think that would be that easy.

It’s not easy – I’ve done it – but it’s more efficient, and probably  
the right thing to do for most use cases. Basically, you determine  
the level of each node, and go from there.

Jaap, as you’ve described your use case elsewhere as being a  
progressive drilldown, you may actually want to use the less efficient  
structure here – I’m not sure. However, using nested sets will mean  
that you’re not stuck with that through your whole application.

> > ```
> > end
> > 
> > ```
> > 
> > Felix Schäfer
> 
> Danke Felix!
> 
> I’m quite unsure whether this would limit the number of queries.

Instead of posting that you’re “quite unsure” whether this would work,  
try it and find out.

> I  
> already call children.each in my views, so this would probably, in  
> terms of performance, won’t really help me.

I think it will help, if you’ve already fetched the whole set. I  
believe (but am not absolutely certain) that the tree is cached, so  
once you do @root.descendants, the whole thing is fetched, and  
@root.children[0].children will not generate another query. Again,  
try it and watch the SQL.

> I was told I could perform  
> this using one single query, but that seems impossible.

What’s impossible about it?

> And that  
> brings me back to a point where I question the advantages of using  
> awesome\_nested\_set over using acts\_as\_tree in combination with  
> acts\_as\_list.

At this point, you’re going around in circles. Stop theorizing and  
actually write some code.

> The thing is, it all made sense! And it would have been perfect.

So try it!

## Best,

Marnen Laibow-Koser  
[http://www.marnen.org](http://www.marnen.org)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![jhaagmans](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc8723/32.png) [@jhaagmans](https://www.ruby-forum.com/u/jhaagmans)\
**Post date:** [August 21, 2009, 4:22pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153/7 "2009-08-21T16:22:28Z")

</div>

> It’s not easy – I’ve done it – but it’s more efficient, and probably  
> the right thing to do for most use cases. Basically, you determine  
> the level of each node, and go from there.

Could you share some of the code? It would really help.

> Jaap, as you’ve described your use case elsewhere as being a  
> progressive drilldown, you may actually want to use the less efficient  
> structure here – I’m not sure. However, using nested sets will mean  
> that you’re not stuck with that through your whole application.

The thing is, elsewhere in my application I will have to go -a lot-  
deeper than this. Certain attributes have other functionality than  
others and I will need to be able to visualize some attributes as a  
table component instead of a list item, for example.

> > > if root.children {  
> > > You’d then just have to call the helper on the root node in your view.
> 
> > > Mit freundlichen Grüßen,
> 
> > > Felix Schäfer
> 
> > Danke Felix!
> 
> > I’m quite unsure whether this would limit the number of queries.
> 
> Instead of posting that you’re “quite unsure” whether this would work,  
> try it and find out.

I did, of course, but it makes just as many queries as far as I can  
see. It will make one query for all children, that’s true, but my  
first implementation did that too. If you call children.each it will  
generate one query for all those children. However, any grandchildren  
(lol) will need a seperate query.

However, this brought me an idea: I hope I got this right of course,  
but based on my perception of a nested set, I could get all  
descendants, order them by “lft” and do something like  
descendants.each do |attribute| and send along the attribute’s id. If  
the parent\_id of the next attribute matches this id, it’s a child,  
otherwise it’s a sibling. However, this makes awesome nested set a  
very limited plugin and if this is the only way this could be done, it  
could really do with some improvements.

I will first try this in my views, so I’ll get back to you on this,  
but it’s not pretty and I do not prefer to do it this way.

> > I  
> > already call children.each in my views, so this would probably, in  
> > terms of performance, won’t really help me.
> 
> I think it will help, if you’ve already fetched the whole set. I  
> believe (but am not absolutely certain) that the tree is cached, so  
> once you do @root.descendants, the whole thing is fetched, and  
> @root.children[0].children will not generate another query. Again,  
> try it and watch the SQL.

It does. It won’t generate a query for @root.descendants, but for  
@root.children and for @root.children[0].children. Only if you say  
@attributes = @root.children and, for example, do something like:

\<% @attributes.each do |attribute| %\>  
\<%= attribute.name %\>  
\<% end %\>

… it will not generate another query after defining @attributes. But  
that still means I will have to work with alot of queries.

> > I was told I could perform  
> > this using one single query, but that seems impossible.
> 
> What’s impossible about it?

Well, there’s no way I know of to be able to say @attributes =  
@root.descendants and use the functionality of awesome nested set to  
descend into my sets without generating alot of queries. In fact,  
stating @attributes = @root.descendants doesn’t do anything, according  
to my logs. The only thing I could do with it is manually handling  
every row and checking whether its parent\_id is the same as the last  
row’s id. I don’t need a a nested set plugin for that!

> > And that  
> > brings me back to a point where I question the advantages of using  
> > awesome\_nested\_set over using acts\_as\_tree in combination with  
> > acts\_as\_list.
> 
> At this point, you’re going around in circles. Stop theorizing and  
> actually write some code.

Why do you assume I didn’t? I’ve tried it and compared logs. It  
doesn’t do anything different query-wise.

Thank you for your help, I really appreciate it.

---

<div class="post-metadata">

**Author:** ![jhaagmans](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc8723/32.png) [@jhaagmans](https://www.ruby-forum.com/u/jhaagmans)\
**Post date:** [August 21, 2009, 4:37pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153/8 "2009-08-21T16:37:06Z")

</div>

> but it’s not pretty and I do not prefer to do it this way.  
> I’ve tried this and it won’t work. The only way I can get this to work  
> properly is by using attribute.level, but that, again, generates a  
> query for every partial (which means even more queries than before).  
> That’s not the way to go. I’m stuck and I have to move on, so for the  
> time being I’ll use my old acts\_as\_tree with acts\_as\_list model,  
> simply because it’s more suitable for repositioning within parents (I  
> won’t be doing anything else). If anyone can come up with a better  
> solution, you’re free to share it and it probably won’t be very hard  
> to convert to it, so please think along 🙂

Thank you both again!

---

<div class="post-metadata">

**Author:** ![jhaagmans](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/bc8723/32.png) [@jhaagmans](https://www.ruby-forum.com/u/jhaagmans)\
**Post date:** [August 21, 2009, 9:12pm UTC](https://www.ruby-forum.com/t/unnecessary-queries/174153/9 "2009-08-21T21:12:16Z")

</div>

Just a quick update to say that I’m back at the situation described in  
my first post. I’ve decided to got with awesome\_nested\_set including  
sortable\_element\_for\_nested\_set, which can do sorting for these nested  
sets. I hope it works out and I hope somewhere along the line I can  
find the advantages of using awesome\_nested\_set in terms of  
performance.

I would love to see a snippet of the code you were talking about,  
Marnen. It could help alot!