SQL2: order node by path | Community
Skip to main content
A
Anonymous
October 16, 2015
Solved

SQL2: order node by path

  • October 16, 2015
  • 4 replies
  • 2294 views

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!

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by smacdonald2008

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. 

4 replies

A
Anonymous
October 16, 2015

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!

    A
    Anonymous
    October 16, 2015

    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!

    smacdonald2008
    smacdonald2008Accepted solution
    Level 10
    October 16, 2015

    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. 

    E
    EvgenyZhivoglyadov
    June 30, 2022

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

    Terms & ConditionsAccessibility statement

    Loading footer...