sql - 节点属性和所有子节点中的 JCR SQL2 全文搜索

Question

我正在尝试在 JCR SQL2 中进行全文搜索。查询应返回所有节点，这些节点至少具有一个包含搜索字符串的属性，或者它们的子节点的属性包含相同的字符串。这是我到目前为止所拥有的:

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

这解决了第一部分，选择具有指定 ocm_classname 和至少一个包含单词“java”的属性的所有节点。但我无法弄清楚如何搜索不具有包含单词“java”的属性但具有包含该单词的属性的子节点的节点。例如应该找到这个节点:

<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>

Answer 1

您需要使用 JOIN 子句，但在 WHERE 子句中使用 OR:

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') )

这将创建两个选择器，parent 和 child，并使用 ISCHILDNODE 连接条件来确保 child< 中的节点 选择器是 parent 选择器中节点的子节点。然后，它使用 OR 标准在结果中包含包含“java”的父节点或包含“java”的子节点。