NXQL query for retrieving document versions, filtered by parent id

[kfrapin_]

Hello, I'm currently trying to retrieve all the versions of a document using a NXQL query, filtering them by their parent id.

Here is my current NXQL query :

SELECT * FROM MyDocumentType 
  WHERE ecm:parentId = '63ab5b87-6e28-4deb-929b-5b5259bcc143'
  AND ecm:currentLifeCycleState <> 'deleted'
  AND MySchema:myattribute = 'my-value'
  AND ecm:isVersion = 1

This query returns no document (using curl) :

{
   "totalSize" : 0,
   "entity-type" : "documents",
   "currentPageSize" : 0,
   "numberOfPages" : 1,
   "isLastPageAvailable" : false,
   "isNextPageAvailable" : false,
   "maxPageSize" : 1000,
   "isPaginable" : true,
   "entries" : [],
   "isSortable" : true,
   "errorMessage" : null,
   "currentPageIndex" : 0,
   "hasError" : false,
   "pageCount" : 1,
   "pageSize" : 0,
   "pageIndex" : 0,
   "resultsCount" : 0,
   "isPreviousPageAvailable" : false
}

This is weird, because when I execute the same query without the ecm:parentId statement, the document versions are returned :

{
   "pageSize" : 0,
   "isSortable" : true,
   "entries" : [
      {
         "changeToken" : "1462376077045",
         "entity-type" : "document",
         "path" : "/my_path/title-1234",
         "uid" : "48a6196a-2fd9-470d-8fa5-c899a344276b",
         "title" : "title-1234",
         "state" : "project",
         "parentRef" : "63ab5b87-6e28-4deb-929b-5b5259bcc143",
         "lastModified" : "2016-05-04T15:34:37.04Z",
         "type" : "MyDocumentType",
         "isCheckedOut" : false,
         "facets" : [
            "Immutable",
            "Versionable",
            "Publishable",
            "Commentable",
            "HasRelatedText",
            "Thumbnail"
         ],
         "repository" : "default"
      },
      {
         "path" : "/my_path/title-1234",
         "changeToken" : "1462785279786",
         "entity-type" : "document",
         "parentRef" : "63ab5b87-6e28-4deb-929b-5b5259bcc143",
         "lastModified" : "2016-05-09T09:14:39.78Z",
         "state" : "project",
         "title" : "title-1234",
         "uid" : "7cbbf41d-bb33-4b4a-971c-b2d4a5cd599c",
         "type" : "MyDocumentType",
         "isCheckedOut" : false,
         "repository" : "default",
         "facets" : [
            "Immutable",
            "Versionable",
            "Publishable",
            "Commentable",
            "HasRelatedText",
            "Thumbnail"
         ]
      }
],
   "isPreviousPageAvailable" : false,
   "currentPageIndex" : 0,
   "isNextPageAvailable" : false,
   "pageIndex" : 0,
   "isPaginable" : true,
   "resultsCount" : 2,
   "totalSize" : 2,
   "currentPageSize" : 2,
   "hasError" : false,
   "entity-type" : "documents",
   "errorMessage" : null,
   "pageCount" : 1,
   "maxPageSize" : 1000,
   "numberOfPages" : 1,
   "isLastPageAvailable" : false
}

For information my document versions have been created using the Document.CheckInoperation with the parameter version set to major

I have probably misunderstood how the document versioning works, or how NXQL works, that's why I'm asking for some help!

If you need more informations, feel free to ask me.

Thanks! Kevin.

[Florent_Guillau · Accepted solution]

Versions's path returned by ver.getPathAsString() are really a pseudo-path (and a historical remnant), but in the actual storage and in what NXQL sees, versions have no parent. You can identify the versions of a given live document using their ecm:versionVersionableId.

[kfrapin_]

Thanks a lot Florent. It works.

Now I proceed with two different queries.

The first query (retrieving the live document) :

SELECT * FROM MyDocumentType
  WHERE ecm:parentId = '63ab5b87-6e28-4deb-929b-5b5259bcc143'
  AND ecm:currentLifeCycleState <> 'deleted'
  AND MySchema:myattribute = 'my-value'

The second query (retrieving all the versions of the found document) :

SELECT * FROM MyDocumentType
  WHERE ecm:versionVersionableId = '<live-document-id-found-with-first-query>' 
  AND ecm:currentLifeCycleState <> 'deleted'