How to render a tree from an awesome_nested_set and hit the database only once?

Question

As the title states, I would like to render an ul-li-tree based on an awesome_nested_set model and hit the database only once. I'm using Ruby on Rails 4.1.

My query looks like this:

Page.root.self_and_descendants.where('depth < ?', 2)

What would be an efficient way to do this?

How can I iterate recursively through it, without loosing the where condition? For example, when I ask every Page instance, if it's a leaf (page_instance.leaf?), so it wouldn't stop at a depth of 2, but it wouldn't hit the database again until I digging deeper than 2. Can someone help me with the next foot step?

This question is related to a sitemap.

Answer 1

If you really want to do it without any extra db queries, you may need to get them all and convert it into an array with .to_a and then re-create the tree structure in memory by iterating and constructing a hash.

However, there is something called a "closure tree" that is a really powerful way to do tree structures really fast in SQL, that some guys much smarter than me figured out. There is a ruby/rails gem that does it for you, and one of its features is to get an entire tree in a single SELECT statement, and puts it into a nested hash structure which would be perfect for your recursive iteration you're talking about. You may want to look into it: https://github.com/mceachen/closure_tree

But really, if your tree isn't that big, you may want to avoid the "premature optimization" and just do it the easy way, hit the database a few to a few dozen times (if you're always going to stop at level 2, the maximum number of queries is the number of children of the root).