-
1. Re: Get nodes by child in search query
rhauch Nov 26, 2013 8:50 AM (in response to fazileh)To do this in JCR-SQL2, you have to declare a selector (e.g., table) for both the parent node and the child of that parent, and you have to use a join to constrain that the child node is actually a child of the parent. Now, I'm not sure whether the wildcard in the LIKE clause you used wanted the parent node to be directly under '/users/admin/send' or some descendant under '/users/admin/send'.
A query that only uses the standard grammar that does the latter is:
SELECT parent.* FROM [nt:unstructured] AS parent JOIN child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE('/users/admin/send',parent) AND NAME(child) = 'user4'
Thus a node at '/users/admin/send/a/b/c' might satisfy the criteria as long as there was also a node at '/users/admin/send/a/b/c/user4'.
If, however, you did not want to allow the parent to be anywhere below the '/users/admin' node, then you should use an ISCHILDNODE constraint instead:
SELECT parent.* FROM [nt:unstructured] AS parent JOIN child ON ISCHILDNODE(child,parent) WHERE ISCHILDNODE('/users/admin/send',parent) AND NAME(child) = 'user4'
Note these queries are basically the same except on line 3.
BTW, ModeShape's implementation does support a few extra functions, so you could instead do this:
SELECT parent.* FROM [nt:unstructured] AS parent JOIN child ON ISCHILDNODE(child,parent) WHERE PATH(parent) LIKE '/users/admin/send/%' AND NAME(child) = 'user4'
The problem with this approach is that its hard to remember/know whether the wildcard will apply just to a single path segment or to all paths. Anyone reading the query has to know the behavior. (Really, this query should be identical to the first query I listed above, since LIKE is not really aware of path segments and simply treats the path as a string.) Because this query is not as explicit, I'd recommend not using it. (The "PATH" function was added because it can be used with other comparison operators, such as <, >, <=, and >=, which can't be done any other way.)
Hope this helps.