Example CMIS queries

Paolo Predonzani edited this page Dec 26, 2017 · 3 revisions

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.

Install a custom content model

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.

Documents of a given type

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

SELECT *
FROM owd:dossier AS d

Documents with a given attribute value

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'

Documents created by a given user

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'

Documents created by ‘myself'

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.

Documents updated this month

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}'

Running the examples

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:

cmis examples running on alfresco-inboxes

You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.