Pagination

This discussion started up in #58 and is over in Stack Overflow. I copied my response in SO to here.

The solution tended to sort by id and pull back only roots. Then a second query to bring back all children from there. This is a great solution because it works great with other sorting orders like name or updated_at.

Alternatively, the solution documented here works best if you are on a forum and you want nodes sorted by creation date.

You can pick the solution that best meets your needs.

Sort by id

Assuming you are sorting by creation date, you can sort by id. It works for a vast majority of all cases.

Fields to sort

If you sort by ancestry (materialized path column), then you get all root nodes up front, and then the children, possibly hundreds of records later.

So you want to sorting by ancestry and id together:

coalesce(ancestry, '0') || '/' || id     -- postgres, sqlite 
concat(coalesce(ancestry, '0'), '/', id) -- postgres, mysql

The records will be in the correct order and you can paginate in the regular way from there.

Ruby to do this

@posts  = @topic.posts.chronological.paginate :page => params[:page]

class Post
  scope :chronological, -> { order("coalesce(ancestry,'0')||'/'|| id") }
end

Indexes

In postgres, you can create an index on post_id, id, ancestry or post_id, order("coalesce(ancestry,'0')||'/'|| id").

Collation

Please check out the documentation on collation in the readme. This is important for this solution to work properly

Feedback

I only tested the sql in a sql prompt and have not tested this in ruby. Please share your successes and improvements.