rails ancestry pagination

Question

I've just followed the Railscast tutorial:

http://railscasts.com/episodes/262-trees-with-ancestry

Is it possible to paginate results from Ancestry which have been arranged? eg: Given I have the following in my Message controller:

def index
  @messages = Message.arrange(:order => :name)
end

Then how would I paginate this as it's going to result in a hash?

Update I found that if I use .keys then it will paginate, but only the top level not the children.

Message.scoped.arrange(:order => :name).keys

Update Each message has a code and some content. I can have nested messages

Suppose I have

code - name

1 - Test1
  1 - test1 sub1
  2 - test1 sub2
2 - Test2
  1 - test2 sub1
  2 - test2 sub2
  3 - test2 sub3

This is how I want to display the listing, but I also want to paginate this sorted tree.

Answer 1

It is possible but I've only managed to do it using two database trips.

The main issue stems from not being able to set limits on a node's children, which leads to either a node's children being truncated or children being orphaned on subsequent pages.

An example:

id: 105, Ancestry: Null
id: 117, Ancestry: 105
id: 118, Ancestry: 105/117
id: 119, Ancestry: 105/117/118

A LIMIT 0,3 (for the sake of the example above) would return the first three records, which will render all but id:119. The subsequent LIMIT 3,3 will return id: 119 which will not render correctly as its parents are not present.

One solution I've employed is using two queries:

1. The first returns root nodes only. These can be sorted and it is this query that is paginated.
2. A second query is issued, based on the first, which returns all children of the paginated parents. You should be able to sort children per level.

In my case, I have a Post model (which has_ancestry) . Each post can have any level of replies. Also a post object has a replies count which is a cache counter for its immediate children.

In the controller:

roots  = @topic.posts.roots_only.paginate :page => params[:page]
@posts = Post.fetch_children_for_roots(@topic, roots)

In the Post model:

named_scope :roots_only, :conditions => 'posts.ancestry is null'

def self.fetch_children_for_roots(postable, roots)
  unless roots.blank?
    condition = roots.select{|r|r.replies_count > 0}.collect{|r| "(ancestry like '#{r.id}%')"}.join(' or ')
    unless condition.blank?
      children = postable.posts.scoped(:from => 'posts FORCE INDEX (index_posts_on_ancestry)', :conditions => condition).all
      roots.concat children
    end
  end
  roots
end

Some notes:

• MySQL will stop using the ancestry column index if multiple LIKE statements are used. The FORCE INDEX forces mySQL to use the index and prevents a full table scan
• LIKE statements are only built for nodes with direct children, so that replies_count column came in handy
• What the class method does is appends children to root, which is a WillPaginate::Collection

Finally, these can be managed in your view:

  =will_paginate @posts  
  -Post.arrange_nodes(@posts).each do |post, replies|
    =do stuff here

The key method here is arrange_nodes which is mixed in from the ancestry plugin and into your model. This basically takes a sorted Array of nodes and returns a sorted and hierarchical Hash.

I appreciate that this method does not directly address your question but I hope that the same method, with tweaks, can be applied for your case.

There is probably a more elegant way of doing this but overall I'm happy with the solution (until a better one comes along).