Uploaded image for project: 'ModeShape'
  1. ModeShape
  2. MODE-1679

JCR-SQL2 JOIN doesn't work for multi-value reference properties

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 3.0.0.CR3
    • 3.0.0.CR2
    • JCR, Query
    • None
    • Workaround Exists
    • Hide

      Use a subquery for the third JOIN. For example:

      SELECT artifact.*
FROM [sramp:baseArtifactType] AS artifact
JOIN [sramp:relationship] AS relationship ON ISCHILDNODE(relationship, artifact)
WHERE relationship.[sramp:relationshipType] = 'element'
  AND artifact.[sramp:artifactType] = 'Part'
  AND relationship.[sramp:relationshipTarget] IN (
        SELECT [jcr:uuid] FROM [sramp:baseArtifactType] AS target
        WHERE target.[ncName] = 'find'
      )

      rather than

      SELECT artifact.*
   FROM [sramp:baseArtifactType] AS artifact
 JOIN [sramp:relationship] AS relationship ON ISCHILDNODE(relationship, artifact)
 JOIN [sramp:baseArtifactType] AS target ON relationship.[sramp:relationshipTarget] = target.[jcr:uuid]
  WHERE artifact.[sramp:artifactType] = 'Part'
   AND relationship.[sramp:relationshipType] = 'element'
   AND target.[ncName] = 'find'
      Show
      Use a subquery for the third JOIN. For example: SELECT artifact.* FROM [sramp:baseArtifactType] AS artifact JOIN [sramp:relationship] AS relationship ON ISCHILDNODE(relationship, artifact) WHERE relationship.[sramp:relationshipType] = ' element ' AND artifact.[sramp:artifactType] = 'Part' AND relationship.[sramp:relationshipTarget] IN ( SELECT [jcr:uuid] FROM [sramp:baseArtifactType] AS target WHERE target.[ncName] = 'find' ) rather than SELECT artifact.* FROM [sramp:baseArtifactType] AS artifact JOIN [sramp:relationship] AS relationship ON ISCHILDNODE(relationship, artifact) JOIN [sramp:baseArtifactType] AS target ON relationship.[sramp:relationshipTarget] = target.[jcr:uuid] WHERE artifact.[sramp:artifactType] = 'Part' AND relationship.[sramp:relationshipType] = ' element ' AND target.[ncName] = 'find'

      Trying to do a JOIN using a multi-value reference property as part of the join condition. Will attach a Maven project that illustrates the problem.

        1. modeshape-multi-value-references.zip
          8 kB

              rhauch Randall Hauch (Inactive)
              ewittman@redhat.com Eric Wittmann
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: