awesome nested set order by

Question

Im using the awesome nested set plugin for ruby on rails. How do I go about sorting by like :name column or something ?

Currently shows the tree like

A
- C
- B

I want it like

A
- B
- C

Answer 1

Already implemented in awesome_nested_set

order_column: on which column to do sorting, by default it is the left_column_name. Example: acts_as_nested_set :order_column => :position

and closure_tree

If you want a specific order, add a new integer column to your model in a >migration:

t.integer :sort_order

and in your model:

class OrderedTag < ActiveRecord::Base
  has_closure_tree order: 'sort_order'
end

Answer 2

Unfortunately it's impossible now. In their class written that "odering by an other column than lft does not work" (lib/awesome_nested_set.rb)

Answer 3

I confirm what @kr00lix said. My way to bypass that problem :

@item_children = @item.children
@item_children = @item_children.sort_by!(&:your_sort_column)

But I agree that in order to avoid useless memory consumption, it would be much nicer to set the order in the SQL command directly.

Answer 4

Doing this overrides the database sort:

@item.children.except(:order).order("your_sort_column")

Example:

organization.self_and_descendants.to_sql
=> "SELECT `organizations`.* FROM `organizations`  WHERE (`organizations`.`lft` >= 1 AND `organizations`.`lft` < 54) ORDER BY `organizations`.`lft`" 

organization.self_and_descendants.except(:order).order("organization_nm").to_sql
=> "SELECT `organizations`.* FROM `organizations`  WHERE (`organizations`.`lft` >= 1 AND `organizations`.`lft` < 54) ORDER BY organization_nm" 

Answer 5

I was able to do this in Rails with recursion:

def add_self_and_children
  [self, children.sort_by{|e| e.name}.map{|c| c.add_self_and_children}].flatten
end

Then call Model.root.add_self_and_children.

But obviously this involves a series of massive database hits.

So if someone who knows more about SQL recursion than I wants to convert this to pure SQL, that would be magic!

BTW, for some reason the following, which would have been a little kinder on the database, did not work:

def add_self_and_children
  [self, children.order(:name).map{|c| c.add_self_and_children}].flatten
end

Answer 6

After implementing Steven_Noble's answer a couple weeks ago, I finally had time to come back and refactor this to a solution that doesn't create N database hits. I added the below method to my nested set model (e.g. Category):

  def sorted_heir_list(target = self, set = self.descendants)
    sorted_list = [target]
    kids = set.select{|i| i.parent_id == target.id}.sort_by{|j| j.name}
    kids.each do |k|
      sorted_list.concat(sorted_heir_list(k, set))
    end
    sorted_list
  end

It's still recursive and a bit verbose, and can probaby be improved a bit further, but now you can call category.sorted_heir_list and get a flat array of options sorted by name, while retaining the heirarchy. Useful for dropdown select options!

NB: I used kids instead of children specifically to avoid confusion with the child functions defined in awesome_nested_set.