Example CMIS queries - softwareloop/alfresco-inboxes GitHub Wiki

After reading about the basic configuration, you may have wondered if a CMIS query can only retrieve documents from a given folder. If this was the case, you could forget about the alfresco-inbox plugin and stick to the standard document library. After all, Share's document library is a great tool to explore folders.

Luckily, CMIS queries are much more powerful, as this page will demonstrate through a series of examples. CMIS queries can run across the folder structure, retrieving documents from any part of the repository based on document type and attributes.

CMIS queries show their best when the repository is full of interesting content models. To follow this page, it is advisable to install Alfresco Workdesk, which includes a basic HR content model.

Download Alfresco Workdesk community 4.2.0-2014-06-30, unzip the file and follow the instructions at bin/SampleApplication/HumanResourcesApplication/readme.txt. You only need to install the custom model (using the install-workdesk-hr.bat or install-workdesk-hr.sh script). You don't have to start the Workdesk webapp.

The model contains custom types to manage HR documents and dossiers. These are perfect to run our CMIS examples.

Simply put the type (full name including namespace) in the FROM clause:

SELECT *
FROM owd:dossier AS d

The WHERE clause can filter by any attribute:

SELECT *
FROM owd:hrdocument
WHERE owd:hrDocumentType = 'Picture'

Some attributes (like ‘state', ‘status', etc) lend themselves to be interpreted as workflow states. It is a common requirement to have an inbox per workflow state.

SELECT *
FROM owd:dossier
WHERE owd:dossierStatus = 'Active'

or

SELECT *
FROM owd:dossier
WHERE owd:dossierStatus = 'Retired'

In workflows, it is frequently required to filter by document owner, assignee, approver, etc. In CMIS it is possible to filter by a given user:

SELECT *
FROM cmis:document
WHERE cmis:createdBy = 'mjackson'

In the previous query, user ‘mjackson' is hard-wired. This is not very useful. Generally you want to filter by the user that is currently logged in, so you can implement the idea of "My documents" or "Documents for my approval".

CMIS does not have a syntax to indicate the currently logged-in user, so alfresco-inboxes implements it via parameterised queries. Parameters are enclosed in brackets. userName is the parameter for the currently logged in user:

SELECT *
FROM cmis:document
WHERE cmis:createdBy = '{userName}'

So, running this query as different users will produce different results.

Alfresco-inboxes supports other parameters related to dates:

• todayStart / todayEnd
• weekStart / weekEnd
• monthStart / monthEnd
• yearStart / yearEnd

Here is a query that retrieves all documents modified this month:

SELECT *
FROM cmis:document
WHERE cmis:lastModificationDate >= '{monthStart}'

Create an inboxes.get.config.xml file with the following content:

<inboxes>
    <group id="CMIS examples">
        <inbox id="All dossiers" iconClass="foundicon-folder">
            <query><![CDATA[
            SELECT *
            FROM owd:dossier AS d
            ]]></query>
        </inbox>
        <inbox id="Pictures" iconClass="foundicon-address-book">
            <query><![CDATA[
            SELECT *
            FROM owd:hrdocument
            WHERE owd:hrDocumentType = 'Picture'
            ]]></query>
        </inbox>
        <inbox id="Active" iconClass="foundicon-checkmark">
            <query><![CDATA[
            SELECT *
            FROM owd:dossier
            WHERE owd:dossierStatus = 'Active'
            ]]></query>
        </inbox>
        <inbox id="Retired" iconClass="foundicon-remove">
            <query><![CDATA[
            SELECT *
            FROM owd:dossier
            WHERE owd:dossierStatus = 'Retired'
            ]]></query>
        </inbox>
        <inbox id="By mjackson" iconClass="foundicon-page">
            <query><![CDATA[
            SELECT *
            FROM cmis:document
            WHERE cmis:createdBy = 'mjackson'
            ]]></query>
        </inbox>
    </group>
    <group id="Parameterised queries">
        <inbox id="By myself" iconClass="foundicon-page">
            <query><![CDATA[
            SELECT *
            FROM cmis:document
            WHERE cmis:createdBy = '{userName}'
            ]]></query>
        </inbox>
        <inbox id="Modified this month" iconClass="foundicon-page">
            <query><![CDATA[
            SELECT *
            FROM cmis:document
            WHERE cmis:lastModificationDate >= '{monthStart}'
            ]]></query>
        </inbox>
    </group>
</inboxes>

Place it into your alfresco-inboxes installation, refresh the web script and reload the plugin's page.

Results may vary depending on your repository's content but you should see something like this: