Thanks! I tried using ISDESCENDANTNODE, but for some reason it wasn't working. Using your syntax worked fine.
However, there seems to be a significant performance difference between the same query in JCR-SQL2 and XPATH So, for example, this comes back immediately: /jcr:root/fm:profiles/com/gmail/wbustraan/*...@fm:session = '3b7856fb-6a5d-427d-a7d6-00af15d7178a'] While this takes about 10 seconds: select * from [nt:base] where isdescendantnode([/fm:profiles/com/gmail/wbustraan]) and [fm:session]= '3b7856fb-6a5d-427d-a7d6-00af15d7178a' Is this a known issue with the new JCR-SQL2 queries or am I doing something wrong? On Fri, Dec 4, 2009 at 4:12 AM, Philipp Bunge <[email protected]> wrote: > Hi Wes > >> I have a bit of a dilemma. I'm working with Jackrabbit 2.0b3 and >> trying to migrate to JCR2.0. However, since the XPATH query syntax is >> deprecated, I'm investigating how to go about writing queries in >> JCR-SQL2. Unfortunately, I haven't come up with many examples or >> tutorials for the new JCR-SQL2 syntax. > > There really aren't many tutorial around as far as I know. I use the > railroad diagrams that Thomas Müller created quite a lot as a > reference however: http://www.h2database.com/jcr/grammar.html > >> One of the scenarios that I'd like to use frequently is searching a >> subtree, i.e. starting at a particular node and searching all >> descendants for certain property values. This is trivial to accomplish >> in XPATH, but I can't figure out the correct (and efficient) way to do >> it using SQL2. Can any one help me out with an example? > > You probably want something as follows: > > SELECT * FROM [nt:unstructured] > WHERE ISDESCENDANTNODE([/the/particular/node]) > AND yourproperty = "foo" > > > Cheers, > Philipp >
