Hey all,
I have the following SQL2 query where I am retrieving documents that have an associated notification. Users have notification nodes under their home path directory, with a documentPath attribute containing the path to the document. Users can have multiple notification nodes referencing the same document path. Is there a way to retrieve the list of unique document paths (no duplicates) of these documents?
Currently, the below simple query will return duplicate documents if there are multiple notification nodes referencing the same document. I thought DISTINCT would mitigate that unless I am misunderstanding the spec?
SELECT DISTINCT document.[jcr:path] from [cq:Page] as document
INNER JOIN [nt:unstructured] as notification on document.[jcr:path] = notification.[documentPath]
where isdescendantnode(document, '/content/experience-fragments/documents') and isdescendantnode(notification, '/home/users/bluh')
The reason I would like the uniqueness to occur in oak is because I will be ordering the documents according to a specific property, so sorting in java will require returning the entire set of documents which is not feasible.
EDIT: CRXDE omits duplicates on the Javascript level which is confusing when testing the query since the result actually does contain duplicate documents! – You can validate that by observing the sentence that says “# results” under the list of nodes is larger than the number of nodes presented.
(not 2)
Or by checking the JSON response after hitting the “execute” button. The best way to test this is to use the QueryManager API directly.