Right now it just executes the :hash_tree method for each root. So it's potentially a O(N) query. It can be done in one swipe, preloading by generation including the roots:
scope = Category.select("categories.*, category_hierarchies.*").
joins("left join category_hierarchies on ancestor_id = categories.id").
order("parent_id IS NOT NULL, generations ASC, name")
You're correct, the class #hash_tree method does do 1 select for every root, but your scope won't work, as it hides the root nodes, nor does it respect the :limit_depth option.
I'll see what I can do.
v3.6.3 uses 1 query now.
You are right it does not take into account the :limit_depth option, but the roots are loaded first because of the left join and the parent_id is not null first order key (so roots are listed first). In any case it was just a hint, I didn't mean to provide a comlpete solution... Thanks for citing me in the changelog.
parent_id is not null