JCR SQL2 - result query order as in JCR browser

18 views
Skip to first unread message

adrien_be

unread,
Dec 19, 2017, 2:02:38 PM12/19/17
to user...@magnolia-cms.com

Antti Hietala

unread,
Dec 19, 2017, 2:02:39 PM12/19/17
to user...@magnolia-cms.com
Hi Adrien,

Regarding sorting SQL2 queries, see if this ticket has some hints.
http://jira.magnolia-cms.com/browse/SCRUM-3

adrien_be

unread,
Dec 19, 2017, 2:02:40 PM12/19/17
to user...@magnolia-cms.com
Thanks Antti,

But again there is nothing mentioned about "natural" order as Randall Hauch in his answer on Stackoverflow.

Good to know about those performance issues though.

Lars Fischer

unread,
Dec 19, 2017, 2:02:41 PM12/19/17
to user...@magnolia-cms.com
Because Magnolia is Open Source you could check the source code and see how the items for the JCR browser are retrieved.

But I would not rely on an order without using a specific 'ORDER BY' clause...

adrien_be

unread,
Dec 19, 2017, 2:02:42 PM12/19/17
to user...@magnolia-cms.com
As far as I know (and seen in the code) every time nodes are returned in the same order as displayed in the JCR browser, it is using the JCR API. Not SQL2.

I'd be happy to be proven wrong though :)

Bence Vass

unread,
Dec 19, 2017, 2:02:44 PM12/19/17
to user...@magnolia-cms.com
Hi,

I have found that if you need to get the nodes in their "natural" order, you will need to use XPath. SQL2 returns nodes already sorted by Lucene somehow.

BR

adrien_be

unread,
Dec 19, 2017, 2:02:45 PM12/19/17
to user...@magnolia-cms.com
Thanks for your input BR.

It seems that "setting the property "respectDocumentOrder" to true in your Jackrabbit Search Configuration is the solution c.f. http://wiki.apache.org/jackrabbit/Search#Search_Configuration ".

[i]I am only quoting jfrantzius from Stackoverflow, see http://stackoverflow.com/questions/16980029/jcr-sql2-result-query-order-as-in-jcr-browser/[/i]

This "respectDocumentOrder" property is by default set to false since SQL1.5

The reason why this property is now set to false by default may be related to performances issues (see Antti's link in his answer above). I have not tested this yet but will let you know once I have.

adrien_be

unread,
Dec 19, 2017, 2:02:46 PM12/19/17
to user...@magnolia-cms.com
Two work arounds for this:
1. Rewrite your query into equivalent java code
2. Rewrite your query into equivalent former "JCR SQL" code, see an example in from ticket MSHOP-79 in magnolia GIT repo: http://git.magnolia-cms.com/gitweb/?p=forge/shop.git;a=commitdiff;h=091d82af711fc3bdbbf5b2dbea269ce9cc172dc4

Federico Grilli

unread,
Dec 19, 2017, 2:02:47 PM12/19/17
to user...@magnolia-cms.com
Hi Adrien,

starting from 5.1 respectDocumentOrder will be set again at its default value http://jira.magnolia-cms.com/browse/MAGNOLIA-5286. The reason why it was set and released as false was unfortunately a mistake on my side while working with 5.0 TreeTable scrolling and sorting. I wanted to experiment and see if that setting would improve performance (as I read in JR wiki) but eventually it turned out that, at least in our case, it didn't make any difference. However, I forgot to revert the change and that's how it ended up in the final release.

HTH,

Federico

adrien_be

unread,
Dec 19, 2017, 2:02:49 PM12/19/17
to user...@magnolia-cms.com
Hi Federico,

I am having this problem using Magnolia 4.5.8 though.

Good to know that it's also happening on 5.0 as well & even better that it'll be fixed for 5.1 :)

Cheers,
Adrien

Matteo Pelucco

unread,
Dec 19, 2017, 2:02:50 PM12/19/17
to user...@magnolia-cms.com
Hi guys, this issue [1],[2] is still there:
It seems that what has been told here [3] is not valid (maybe due to Jackrabbit 2.8.0?)
You can see in action also on 5.3.1 (demo.magnolia-cms.com)

repository: category
query: SELECT * FROM [nt:base]
result item type: nt:base
Result:

16 nodes returned in 7ms
/rep:accesscontrol/everyone/rep:policy
/rep:accesscontrol/everyone/rep:policy/entry
/rep:accesscontrol
/rep:accesscontrol/everyone
/Family
/Finance
/Family/relatedUUID/756d0705-d629-479f-9
/Culture
/Local
/International
/Politics
/Family/relatedUUID
/Family/relatedUUID/1124b03d-09d9-41f3-b
/Sports
/Science
/Weather

[1] http://forum.magnolia-cms.com/forum/thread.html?threadId=9e6cbfa9-453e-478f-a640-b532d130ca35
[2] http://jira.magnolia-cms.com/browse/MAGNOLIA-5286
[3] http://stackoverflow.com/questions/16980029/jcr-sql2-result-query-order-as-in-jcr-browser/17042398#17042398

Mikaël Geljić

unread,
Dec 19, 2017, 2:02:51 PM12/19/17
to user...@magnolia-cms.com
Hi Matteo,

I was curious so I quickly gave it a shot...
By default I had "random" ordering of query results with both Jackrabbit 2.6.4 and 2.8.0 — even different kinds of random.

In both cases, I was able to fix it by rebuilding the index, see http://wiki.apache.org/jackrabbit/Search#Rebuilding_the_Index
Then I had query results back in document order again.

Haven't dug further down as to why this indexing is wrong in the first place...

Cheers,

Mika

Joerg von Frantzius

unread,
Dec 19, 2017, 2:02:52 PM12/19/17
to user...@magnolia-cms.com
Hi Adrien,

please see my response on Stackoverflow:

Did you try setting the property
"respectDocumentOrder" to true in your Jackrabbit
Search Configuration ?

If true and the query does not contain an 'order by'
clause, result nodes will be in document order. For better
performance when queries return a lot of nodes set to 'false'
(In 1.5 'false' is now the default).

You should set it to true really only if you need it, because
query results will be iterated fully in Java in order to sort
them.

Regards,

Jörg

On 07.06.2013 13:08, adrien beber (via Magnolia Forums) wrote:

See full question here http://stackoverflow.com/questions/16980029/jcr-sql2-result-query-order-as-in-jcr-browser

thanks in advance

--

Dipl.-Inf.
Jörg von Frantzius, System Architect

Email mailto:joerg.f...@aperto.de

Phone +49 30 283921-318

Fax +49 30 283921-29

Aperto AG - In der Pianofabrik

Chausseestraße 5, D-10115 Berlin-Mitte

http://www.aperto.de

http://www.facebook.com/aperto

https://www.xing.com/companies/apertoag

HRB 77049, AG Berlin Charlottenburg

Vorstand: Dirk Buddensiek (Vorsitzender), Kai Großmann,
Stephan Haagen

Aufsichtsrat: Bernd Hardes (Vorsitzender)
Reply all
Reply to author
Forward
0 new messages