..  _query:

Query
=======================================
CMIS query was made available in Technology One's implementation of CMIS during the 2016A release. It provides a means for users to dynamically retrieve document sets and index entries based on property values.

Queryable properties can be checked via CMIS Workbench:

.. image:: /_static/img/queryable-properties-workbench.PNG
	:alt: Queryable properties using workbench

Workbench example
""""""""""""""""""
Open workbench and press the Query button on the main menu. This will open a new query window that allows for a cmis sql statement to be entered. The cmis spec defines how a query must be structured, however there are several additional constraints that the ECM implementation applies:

:Restricted Indexes: Only indexes that have been whitelisted in CMIS configuration are searchable.

:Operators: CMIS will support the same types of queries that ECM Search supports. This includes equals ``=``, not equals ``<>``, greater than ``>``, less than ``<``, greater than or equal to ``>=``, less than or equal to ``<=``, between ``between 'from' and 'to'``, like ``like 'report%'``, is empty, is not empty, starts with, ends with, contains, and sounds like. The use of these terms will depend on the type of field you are searching on, for example you can not use ``contains`` on a numeric field.
 
            It also supports boolean operators such as ``AND``, ``NOT``, ``OR``; and the use of brackets to group query terms. 
			
:Searchable tables:

	*T1ActiveUserType* provides access to all users and groups that are currently active within ECM.
	
	*T1DocumentType* allows for individual documents to be queried. Note that this will return all documents and versions. This differs from T1FolderType which only returns the latest version.

	*T1FieldDetailType* shows field details which can be used to label document and index entry fields.

	*T1FolderType* provides access to document sets.
	
	*T1IndexEntryType* exposes index entries. Note that user fields can be mapped to their labels via T1FieldDetailType.

	

.. image:: /_static/img/query-1.PNG
	:alt: Querying via Workbench
	
In order to retrieve all document sets authored by *TechOne* that belong to the TechOne QAP the following query would be used:

.. code-block:: sql

	SELECT *
	FROM T1FolderType
	WHERE cmis:author = 'TechOne'
		   AND cmis:templateId = '8'
		   
The ``T1FolderType`` table indicates that the search should be performed across document sets. The author property definition id specifies that results should be restricted to those that have a value of *TechOne*.

The templates Id field works in a similar manner. To determine which id to use, a getChildren() call can be made to the ``QuickAddProfiles`` folder.

Once executed, the query will return any matching document sets. Note that the result set is pageable and may contain zero to one hundred matches per response. 

To retrieve documents that have been created within a date range the following query would be used:

.. code-block:: sql

	SELECT *
	FROM T1FolderType
	WHERE cmis:creationDate between '2016-09-12 08:30:00' and '2016-09-12 15:45:00'
	
To retrieve customers with a description containing a search term you can use:

.. code-block:: sql

	SELECT *
	FROM T1FolderType
	WHERE cmis:author = 'John' AND cmis:description like '%John%'
	
In order to retrieve document sets contained with a list the following query may be used:
		   
.. code-block:: sql

	SELECT *
	FROM T1FolderType
	WHERE cmis:documentSetid Is one of 578453,578452
	
String fields can be used in a similar manner however special characters and punctuation should be avoided:

.. code-block:: sql

	SELECT *
	FROM t1indexentrytype
	WHERE cmis:usertext1 is one of 'Funny,fruit,test test'
	
More complex queries can be performed using a combination of brackets and boolean operators:
		   
.. code-block:: sql

	SELECT *
	FROM T1FolderType
	WHERE cmis:author = 'John' OR (cmis:author contains 'Smith' AND cmis:creationDate > '2016-09-12 08:30:00')


An example of how to retrieve all users where the description starts with Chris:

.. code-block:: sql

	SELECT *
	FROM t1activeusertype
	WHERE cmis:description LIKE 'CHRIS%'


Request Samples
"""""""""""""""

In order to retrieve all level one subjects that have a description of 'Aged Services' the following request would be used.

.. code-block:: xml

	Method: GET
	Url: {base url}/{environment id}/Api/CMIS/{repository id}/query/

	<?xml version='1.0' encoding='UTF-8'?>
	<cmis:query xmlns:cmis="http://docs.oasis-open.org/ns/cmis/core/200908/">
		<cmis:statement>SELECT * FROM t1indexentrytype WHERE cmis:indexnumber = 40 AND cmis:levelNumber = 1 AND cmis:userText1 = 'Aged Services'</cmis:statement>
		<cmis:searchAllVersions>false</cmis:searchAllVersions>
		<cmis:includeAllowableActions>false</cmis:includeAllowableActions>
		<cmis:includeRelationships>none</cmis:includeRelationships>
		<cmis:renditionFilter>cmis:none</cmis:renditionFilter>
		<cmis:maxItems>100</cmis:maxItems>
		<cmis:skipCount>0</cmis:skipCount>
	</cmis:query>

Requests can also be generated using the 'q' url parameter.

::

	Method: GET
	Url: {base url}/{environment id}/Api/CMIS/{repository id}/query/?q=SELECT * FROM t1indexentrytype WHERE cmis:indexnumber = 40 AND cmis:levelNumber = 1 AND cmis:userText1 = 'Aged Services'

Querying for Activer Users and Groups:

To retrieve an active group requires an additional parameter. Either **filter=searchallversions** or **searchallversions=true** can be used.

::

	Method: GET
	Url: {base url}/{environment id}/Api/CMIS/{repository id}/query/?q=select * from t1activeusertype WHERE cmis:name = 'Additional Test Group'&filter=searchallversions

	Method: GET
	Url: {base url}/{environment id}/Api/CMIS/{repository id}/query/?q=select * from t1activeusertype WHERE cmis:name = 'Additional Test Group'&searchallversions=true


DotCMIS Samples
"""""""""""""""

.. code-block:: c#

	// Index Entries
	var queryResults = session.Query("SELECT * FROM t1indexentrytype WHERE cmis:userText1 = 'test name' AND cmis:indexNumber = 40", true);

	// Document sets
	var queryResults = session.Query("SELECT * FROM t1foldertype WHERE cmis:documentSetId = 123", true);
		   
	// Field details
	var queryResults = session.Query("SELECT * FROM t1fielddetailtype WHERE cmis:fieldName = 'Description' AND cmis:indexNumber = 183", true);

	// Active Users only
	var queryResults = session.Query("SELECT * FROM t1activeusertype WHERE cmis:name = 'John Smith'", false);

	// Active Users and Groups
	var queryResults = session.Query("SELECT * FROM t1activeusertype WHERE cmis:name = 'John Smith'", true);