Pagination with materialized paths about ancestry

[nazar]

One solution to that is to pad the number with zeroes, but another is to translate the number to base 36 like Drupal and at least one Django add-on does.

I think there is one more problem the base 36 encoding of ancestry does not solve and that is the issue of limit boundaries hitting an ancestry. Hopefully an example will illustrate this.

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 correctly. The subsequent LIMIT 3,3 will return id: 119 which will not render correctly as its parents are not present.

I'm starting to think that pagination can only happen via two queries: the first paginates the roots; the second returns any children based on parents in the first query. The second query won't be pretty in that there will be a lot of (ancestry like '105%') or (ancestry like '109%') or (ancestry like '115%') and so on.

More thoughts are very welcome....

[nazar]

I managed to come up with the following solution.

[kbrock]

There is also an issue with i18n - in some cases punctuation is ignored. so sorting 12/3/4 and 1/2/3/4 both are treated as 12345

Nice solution.
In it's current state, I don't feel this is doable.

Are you still hoping to get a change in master?
Over the years, have you found a good trade off to simplify this problem?

[kbrock]

I updated the SO article and will add to the wiki.
I'm hoping to come up with better solutions, but at least have one for now. (only took 10 years to find a solution that was not "paginate roots, then add all their children)"