There are two places the ISCHILDNODE
function can be used in a JCR-SQL2 query: in the WHERE
clause and in the join criteria. Unfortunately, they take different parameters.
Your query is attempting to use the ISCHILDNODE
in the join criteria, which requires two parameters: the selector name for the child nodes, and the selector name for the parent nodes.
Here's the query I think you want:
SELECT parent.*
FROM [cq:PageContent] AS parent
INNER JOIN [nt:base] as child ON ISCHILDNODE(child,parent)
WHERE ISDESCENDANTNODE(parent, [/content/grocerystore/food/])
The only change is the parameters to the ISCHILDNODE
function.
Unfortunately, the error message doesn't really make this clear. To understand why, it helps to know about the other form of ISCHILDNODE
- the one used in the WHERE
clause. That form also takes two parameters: the name of the selector that will represent the child nodes, and the literal path of the parent (under which the resulting nodes are children). Here's a contrived query that uses this form:
SELECT node.*
FROM [nt:base] AS node
WHERE ISCHILDNODE(node,[/content/grocerystore])
The query result would contain all nodes that are children of the /content/grocerystore
node.
Now, if the query defines just one selector (e.g., a non-join), then there is only one selector name that can be passed to the ISCHILDNODE
function. Strictly speaking, the selector is implicitly known, so JCR-SQL2 allows you to pass in just the path. Here's a query that is semantically identical to the previous contrived example:
SELECT node.*
FROM [nt:base] AS node
WHERE ISCHILDNODE([/content/grocerystore])
This is the only form of the ISCHILDNODE
that takes a single parameter, and I think this is the form that I think CQ5 is expecting: the error says that the query defines more than one selector, so the selector must be supplied as the first parameter.
Of course, this is very misleading because you're actually using the form of the function that appears in the join criteria. A better error message would say that function requires the child selector name and the parent selector name.