SQL2: order node by path

[Former Community Member]

Hi,

I am working on a structure like this:

/content
        /myapp
                /archive
                    /2014
                        /08
                            page
                            page
                            page
                        /07
                            page
                            page
                    /2013
                        /10
                            page
                            page
                            page
                        /09
                            page
                            page
 

Let's suppose you want to query the crx in order to retrieve these pages. A possible way to achieve this is the following:

select * from [cq:Page] as pageItem where isdescendantnode(pageItem, [/content/myapp/archive])

Everything works fine, but nodes are retrieved in an order that doesn't make much sense to me. I would like them to be ordered by path: in this particular case, this would mean having them ordered by date. I know a similar result could be achieved by setting a property on each node and use it to handle the ordering, but:

1. there could be a huge amount of already existing nodes with no date-related property whatsoever
2. such information would be redundant, given the structure highlighted above

Unfortunately, creation date and last modified date cannot be used: I may create today a page that was meant to be created back in 2010.

Does anyone know how to do it?

Thanks!

[smacdonald2008 · Accepted solution]

Another option you have is to query the data using JCR-SQL2. Place the result set into a Java collection and then order the collection with your own app logic. 

[Former Community Member]

Thank you smacdonald2008, this is exactly the exit strategy I was going to implement; I hoped JCR-SQL2 could provide some way to achieve the same result, even if I couldn't find anything useful in the grammar ( http://www.h2database.com/jcr/grammar.html )

That's a shame, the path is an important "property" of each node (mind the quotes...), and I would have expected JCR-SQL2 to cope with such a problem. Am I the only one feeling like this is one of the major shortcomings (the lack of a "count" function, that we all are well used to use when working on any relational DB, is another of them)?

It may also be a performance issue: since the nodes are retrieved in "some order" (based on some score I am not well aware of), I guess there is already an ordering function running under the hood...Having no way to control and fine tuning this function, I have to run an ordering algorithm again, which is not great, given the fact that there are tens of those pages at the moment, but they may well become hundreds or thousands within a couple of years.

Thank you again anyway!

[Former Community Member]

Hi everyone,

does any of you have a better idea?

It seems to me that performances can become an issue for large datasets...are we sure SQL2 doesn't offer a way to order nodes by path?

Thanks!

[EvgenyZhivoglyadov]

Currently should work with pageItem.[jcr:path] property