NXQL query for locked documents

[Terry_A]

Looking for a way to query for locked documents of a particular type.

I've tried variations on this:

SELECT * FROM Document WHERE ecm:primaryType = 'DocumentType' AND ecm:lockOwner IS NOT NULL

including

SELECT * FROM Document WHERE ecm:primaryType = 'DocumentType' AND (ecm:lockOwner IS NOT NULL or ecm:lockOwner <> ' ')

To test the query syntax I am working in a pre-production environment where I know that some but not all of the documents of the desired types are locked. Regardless of how I word the query, I either get back all of the documents or none of the documents, not the desired subset.

Any suggestions or help is appreciated.

[Gregory_Carlin · Accepted solution]

Hello,

Try with ecm:isCheckedIn = 0 to find locked documents 🙂

More on https://doc.nuxeo.com/nxdoc/nxql/#special-nxql-properties

Regards

[Phil_Ludlow]

I tried your query and the first option worked correctly for me:

SELECT * FROM Document WHERE ecm:primaryType = 'DocumentType' AND ecm:lockOwner IS NOT NULL

That query returned the 1 document I had locked of the specified doctype in the NQXL.

If I use the option for isCheckedIn then it returns all the documents of the specified doctype, not sure why but it doesn't seem to acknowledge that a locked file is checkedOut.

SELECT * FROM Document WHERE ecm:primaryType = 'DocumentType' AND ecm:isCheckedIn = 0

I tested this in my local environment which is LTS 2021 (hotfix 10). If your first query is not working, then maybe it has to do with the database you are using?

[Terry_A]

TBH I'm not sure if it was a database issue or something else, but whatever the case, some of these attributes such as ecm

[opage_]

Hi ! I've got exactly the same problem. What solution did you use ?

The second question will be : how to unlock ALL locked documents (yes ... I have to do this because another software has locked many of them ...)

[opage_]

It doesn't work: It gives me locked and unlocked documents. For example, It returns me this one which is locked

root@gedgfct1# curl http://localhost:8080/nuxeo/api/v1/id/1d9d53df-ba30-443b-9216-6a83854a2c2b -u xxxx:xxxxxx -H 'fetch-document: lock'

{"entity-type":"document","repository":"default","uid":"1d9d53df-ba30-443b-9216-6a83854a2c2b","path":"/default-domain/workspaces/Cocktail/flux/numero:FLX-2022-001516/Bon de commande/Deployments.csv","type":"BonDeCommande","state":"project","parentRef":"0f365e91-ff21-435c-be32-d2ae39b0620d","isCheckedOut":true,"isRecord":false,"retainUntil":null,"hasLegalHold":false,"isUnderRetentionOrLegalHold":false,"isVersion":false,"isProxy":false,"changeToken":"3-0","isTrashed":false,"title":"Deployments.csv","lockOwner":"criecm","lockCreated":"2022-09-19T10:51:09.246Z","lastModified":"2022-09-19T10:51:09.220Z","facets":["Versionable","NXTag","Publishable","Commentable","HasRelatedText","Thumbnail"],"schemas":[{"name":"file_schema","prefix":"file_schema"},{"name":"uid","prefix":"uid"},{"name":"thumbnail","prefix":"thumb"},{"name":"file","prefix":"file"},{"name":"common","prefix":"common"},{"name":"bondecommande","prefix":"bondecommande"},{"name":"files","prefix":"files"},{"name":"dublincore","prefix":"dc"},{"name":"relatedtext","prefix":"relatedtext"},{"name":"facetedTag","prefix":"nxtag"}]}**#**

but also this one which is unlocked

root@gedgfct1# curl http://localhost:8080/nuxeo/api/v1/id/899fac1c-4d23-4971-8885-1dc298ac41e3 -u xxxxx:xxxxx -H 'fetch-document: lock'


  

{"entity-type":"document","repository":"default","uid":"899fac1c-4d23-4971-8885-1dc298ac41e3","path":"/default-domain/workspaces/Cocktail/flux/numero:FLX-2022-000647/Bon de commande/DEP_COMMANDE_LIGNE.png","type":"BonDeCommande","state":"project","parentRef":"74a4feb4-ccf2-4fcb-8533-9dfd95f76d8a","isCheckedOut":true,"isRecord":false,"retainUntil":null,"hasLegalHold":false,"isUnderRetentionOrLegalHold":false,"isVersion":false,"isProxy":false,"changeToken":"3-0","isTrashed":false,"title":"DEP_COMMANDE_LIGNE.png","lastModified":"2022-06-03T08:00:10.747Z","facets":["Versionable","NXTag","Publishable","Commentable","HasRelatedText","Thumbnail"],"schemas":[{"name":"file_schema","prefix":"file_schema"},{"name":"uid","prefix":"uid"},{"name":"thumbnail","prefix":"thumb"},{"name":"file","prefix":"file"},{"name":"common","prefix":"common"},{"name":"bondecommande","prefix":"bondecommande"},{"name":"files","prefix":"files"},{"name":"dublincore","prefix":"dc"},{"name":"relatedtext","prefix":"relatedtext"},{"name":"facetedTag","prefix":"nxtag"}]}**#**

As Terry said "some of these attributes such as ecm:lockOwner and ecm:isCheckedIn were not updating to the database properly for locked documents"

I've opened a case with Nuxeo support

[opage_]

I've found part of the problem: As the "redis" template was in use, the locks are managed by RedisLockManager, hence stored in redis.

The solution is to delete all the keys whose prefix is "nuxeo:lock:"