Introduction

In this tutorial we are going to go through the basics in CMIS query language and after this you would be able to query your alfresco nodes in quick and easy way with CMIS in your fingertips.

How does a simple CMIS query look like?

select * from cmis:document

This will select all of your documents in alfresco repository!

Lets see some examples

In basics we can query documents or folders that belong to table cmis:document and cmis:folder like we have shown above.

WHERE clause is important for us as we do not want to select all of our nodes, so lets see basic example of how to use it.

SELECT * FROM cmis:document WHERE cmis:name = 'test'

ORDER BY clause, is great and it can be used to put order in our results, in example below results will be ordered with date created.

SELECT * FROM cmis:document WHERE cmis:name = 'test' ORDER BY cmis:creationDate

Selecting custom types

It is presumed that you have your own custom types, aspects and properties defined and that you really want to select :), previously we have defined ab:invoice aspect. Our aspect has a property called ab:invNumber.

SELECT * FROM ab:invoice where ab:invNumber = 123 ORDER BY cmis:creationDate

Where clause

Where clause can be a complicated beast and can be combination or ANDs, ORs and NOTs and list of predicates in between.

CMIS query supports next predicates :

• Comparison
• IN
• LIKE
• NULL
• Text Search
• Folder Predicate

Comparison predicates that are supported are =, <, >, <>,<= ,>=. Types that are supported are String, Decimal, Integer, Boolean, Date, ID. There are some minor variations and limitations depending of type.

In predicate can be used the same way as in SQL language and it is a great way for selecting nodes that have properties with multiple values like:

SELECT * FROM ab:invoice WHERE ab:invNumber IN (0, 1, 2, 3)

Null predicate can be used to check if some nodes have property that is not set or it is set depending of NOT keyword.

SELECT * FROM ab:invoice WHERE ab:invNumber IS NULL

SELECT * FROM ab:invoice WHERE ab:invNumber IS NOT NULL

Like predicate can be used for searching for part of the property value, for instance nodes which cmis:name contains specified string value.

SELECT * FROM ab:invoice WHERE cmis:name LIKE '%invoice_2014%'

Text search predicate is used to leverage the alfresco indexing capabilities and to search for text that document contains. See how we have escaped the ‘ to get invoices that contain a phrase .

SELECT * FROM ab:invoice WHERE CONTAINS('\'alfrescoBlog Inc\'');

Folder predicate , we often need to search within specified folder sometimes directly below or at all depths. For this predicate functions IN_FOLDER and IN_TREE are used.

SELECT cmis:name FROM cmis:document WHERE IN_FOLDER('folder id')
SELECT cmis:name FROM cmis:folder F WHERE IN_TREE(F, 'folder id')

Summary

CMIS allow us to easily select nodes that we need writing complex SQL queries. For any additional problems you can contact us or look up the documentation on alfresco wiki.