JCR SQL2 fulltext search in node properties and all child nodes

Question

I'm trying to do a fulltext search in JCR SQL2. The query should return all nodes which have at least one property containing the search string or they have child nodes with properties containing the same string. Here's what I have so far:

select * from [nt:base] as t where ocm_classname = 'info.magnolia.cv.CurriculumVitae' and contains(t.*, 'java')

This solves the first part, selects all nodes with the specified ocm_classname and at least one property containing the word 'java'. But I can't figure out how to search for nodes which don't have properties containing the word 'java' but have child nodes with properties containing the word. For example this node should be found:

<sv:node sv:name="cv1362044004066">
    <sv:property sv:name="jcr:primaryType" sv:type="Name">
        <sv:value>nt:unstructured</sv:value>
    </sv:property>
    <sv:property sv:name="address" sv:type="String">
        <sv:value>Chicago, IL</sv:value>
    </sv:property>
    <sv:property sv:name="currentDepartment" sv:type="String">
        <sv:value>dotNet</sv:value>
    </sv:property>
    <sv:property sv:name="currentRole" sv:type="String">
        <sv:value>Project Manager</sv:value>
    </sv:property>
    <sv:property sv:name="dateOfBirth" sv:type="Date">
        <sv:value>1981-01-14T00:05:00.000-05:00</sv:value>
    </sv:property>
    <sv:property sv:name="id" sv:type="String">
        <sv:value>1362044004066</sv:value>
    </sv:property>
    <sv:property sv:name="name" sv:type="String">
        <sv:value>John Carpenter</sv:value>
    </sv:property>
    <sv:property sv:name="ocm_classname" sv:type="String">
        <sv:value>info.magnolia.cv.CurriculumVitae</sv:value>
    </sv:property>
    <sv:node sv:name="skills">
        <sv:property sv:name="jcr:primaryType" sv:type="Name">
            <sv:value>nt:unstructured</sv:value>
        </sv:property>
        <sv:node sv:name="collection-element">
            <sv:property sv:name="jcr:primaryType" sv:type="Name">
                <sv:value>nt:unstructured</sv:value>
            </sv:property>
            <sv:property sv:name="level" sv:type="String">
                <sv:value>Advanced</sv:value>
            </sv:property>
            <sv:property sv:name="name" sv:type="String">
                <sv:value>Management</sv:value>
            </sv:property>
            <sv:property sv:name="ocm_classname" sv:type="String">
                <sv:value>info.magnolia.cv.CVSkills</sv:value>
            </sv:property>
        </sv:node>
        <sv:node sv:name="collection-element">
            <sv:property sv:name="jcr:primaryType" sv:type="Name">
                <sv:value>nt:unstructured</sv:value>
            </sv:property>
            <sv:property sv:name="level" sv:type="String">
                <sv:value>Advanced</sv:value>
            </sv:property>
            <sv:property sv:name="name" sv:type="String">
                <sv:value>Scrum</sv:value>
            </sv:property>
            <sv:property sv:name="ocm_classname" sv:type="String">
                <sv:value>info.magnolia.cv.CVSkills</sv:value>
            </sv:property>
        </sv:node>
        <sv:node sv:name="collection-element">
            <sv:property sv:name="jcr:primaryType" sv:type="Name">
                <sv:value>nt:unstructured</sv:value>
            </sv:property>
            <sv:property sv:name="level" sv:type="String">
                <sv:value>Advanced</sv:value>
            </sv:property>
            <sv:property sv:name="name" sv:type="String">
                <sv:value>Java</sv:value>
            </sv:property>
            <sv:property sv:name="ocm_classname" sv:type="String">
                <sv:value>info.magnolia.cv.CVSkills</sv:value>
            </sv:property>
        </sv:node>
        <sv:node sv:name="collection-element">
            <sv:property sv:name="jcr:primaryType" sv:type="Name">
                <sv:value>nt:unstructured</sv:value>
            </sv:property>
            <sv:property sv:name="level" sv:type="String">
                <sv:value>Intermediate</sv:value>
            </sv:property>
            <sv:property sv:name="name" sv:type="String">
                <sv:value>Spring</sv:value>
            </sv:property>
            <sv:property sv:name="ocm_classname" sv:type="String">
                <sv:value>info.magnolia.cv.CVSkills</sv:value>
            </sv:property>
        </sv:node>
    </sv:node>
</sv:code>

Randall Hauch Randall Hauch · Accepted Answer · 2013-02-28T15:08:37

You need to use a JOIN clause but use an OR in the WHERE clause:

SELECT parent.* 
FROM [nt:base] AS parent 
INNER JOIN [nt:base] AS child ON ISCHILDNODE(child,parent)
WHERE parent.ocm_classname = 'info.magnolia.cv.CurriculumVitae' 
  AND ( CONTAINS(parent.*, 'java') OR CONTAINS(child.*,'java') )

This creates two selectors, parent and child, and uses an ISCHILDNODE join criteria to ensure that nodes in the child selector are children of the nodes in the parent selector. Then, it uses an OR criteria to include in the results parent nodes that contain 'java' or child nodes that contain 'java'.

JCR SQL2 fulltext search in node properties and all child nodes

1 Answers