Query of document's tags and categories

[oubaalouni]

Hi everyone, 

Could you please tell me if there is a query Cmis or SQL to retrieve a listing of document according to their tags and categories ? 
Thank you

[cesarista]

Tags and categories are not part of CMIS standard. You can only do some things with cmis:item in CMIS 1.1

Cool things you can do in Alfresco with cmis:item support | ECM Architect 

Regards.

--C.

[oubaalouni]

Thank you Cesar for your help. 

Could you guide me into retrieving a listing of all documents existing in alfresco database with their respective categories and tags ? is there an SQL query to get those information ? coz, as much as i know, i've almost checked all tables in Alfresco's database and didn't find any information related to categories or tags, i'm hoping you could enlighten me in this way. 

Thank you very much !!   

[jpotts]

You should not be checking Alfresco's SQL tables for anything. You should be using the Alfresco REST API for everything (or CMIS or web scripts).

For example, here is the API Explorer documentation on getting tags for a node:

Alfresco Content Services REST API Explorer 

Here is what a call with a real node reference looks like:

http://alfresco.local:8080/alfresco/api/-default-/public/alfresco/versions/1/nodes/68b4c440-4e6f-4e5...

Which returns the following for a doc with three tags (test1, test2, and test3):

{
    "list": {
        "pagination": {
            "count": 3,
            "hasMoreItems": false,
            "totalItems": 3,
            "skipCount": 0,
            "maxItems": 100
        },
        "entries": [
            {
                "entry": {
                    "tag": "test1",
                    "id": "1352f817-524a-4c50-82a0-ab8c96cf9e77"
                }
            },
            {
                "entry": {
                    "tag": "test2",
                    "id": "223e9c67-7e87-4fa0-84e3-305b259e080a"
                }
            },
            {
                "entry": {
                    "tag": "test3",
                    "id": "9a1a751a-5b7b-4f72-b1ee-458b9e11e519"
                }
            }
        ]
    }
}

     ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Categories are available by getting the node itself, so given a node reference of 68b4c440-4e6f-4e52-be4b-686357cd42cd, the URL is: http://alfresco.local:8080/alfresco/api/-default-/public/alfresco/versions/1/nodes/68b4c440-4e6f-4e5... which returns the properties of the node, like:

{
    "entry": {
        "isFile": true,
        "createdByUser": {
            "id": "admin",
            "displayName": "Administrator"
        },
        "modifiedAt": "2018-09-18T03:55:53.965+0000",
        "nodeType": "cm:content",
        "content": {
            "mimeType": "text/plain",
            "mimeTypeName": "Plain Text",
            "sizeInBytes": 4,
            "encoding": "UTF-8"
        },
        "parentId": "c76039f4-66a4-4b26-b32b-c6c53edc3482",
        "aspectNames": [
            "cm:titled",
            "cm:generalclassifiable",
            "cm:auditable",
            "app:inlineeditable",
            "cm:taggable",
            "fm:commentsRollup",
            "cm:author",
            "fm:discussable"
        ],
        "createdAt": "2018-09-18T03:17:58.080+0000",
        "isFolder": false,
        "modifiedByUser": {
            "id": "admin",
            "displayName": "Administrator"
        },
        "name": "test.txt",
        "id": "68b4c440-4e6f-4e52-be4b-686357cd42cd",
        "properties": {
            "fm:commentCount": 1,
            "cm:categories": [
                "c705df9c-ab8c-4332-832f-957f0af56ac6",
                "802c7aca-4db3-4293-ac75-7c1c51e105a4",
                "4aa6fa9d-0f1e-40a3-bd20-e89ffdb7e8fc"
            ],
            "app:editInline": true,
            "cm:taggable": [
                "1352f817-524a-4c50-82a0-ab8c96cf9e77",
                "223e9c67-7e87-4fa0-84e3-305b259e080a",
                "9a1a751a-5b7b-4f72-b1ee-458b9e11e519"
            ]
        }
    }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You can see the categories assigned to this node at lines 38, 39, and 40. If you want to know what the human readable categories are you can do additional requests because those are just node references, so, for example, the first one in the list is fetched using http://alfresco.local:8080/alfresco/api/-default-/public/alfresco/versions/1/nodes/c705df9c-ab8c-433... which returns:

{
    "entry": {
        "aspectNames": [
            "cm:auditable"
        ],
        "createdAt": "2017-11-01T04:16:15.410+0000",
        "isFolder": false,
        "isFile": false,
        "createdByUser": {
            "id": "System",
            "displayName": "System"
        },
        "modifiedAt": "2017-11-01T04:16:15.410+0000",
        "modifiedByUser": {
            "id": "System",
            "displayName": "System"
        },
        "name": "Acceptance Plan",
        "id": "c705df9c-ab8c-4332-832f-957f0af56ac6",
        "nodeType": "cm:category",
        "parentId": "ff08ca60-f100-42ac-96ff-10ee97f086f5"
    }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

So that's the "Acceptance Plan" category.

Hope this helps,

Jeff