View source for 2023:CMISQL Query

{{AutoVersion}}

<blockquote>{{#lst:Glossary|CMIS Query}}</blockquote>

Also called a "CMISQL query", the querying language '''''CMIS Queries''''' use is based on a subset of the SQL-92 grammar.  Where SQL is a querying language to search and select data in a database, "CMISQL" is a querying language to search and select documents (and their metadata properties) in a storage location, represented by a '''CMIS Repository''' in Grooper.

== Glossary ==
<u><big>'''AND'''</big></u>: {{#lst:Glossary|AND}}

<u><big>'''Box'''</big></u>: {{#lst:Glossary|Box}}

<u><big>'''CMIS Connection'''</big></u>: {{#lst:Glossary|CMIS Connection}}

<u><big>'''CMIS Lookup'''</big></u>: {{#lst:Glossary|CMIS Lookup}}

<u><big>'''CMIS Query'''</big></u>: {{#lst:Glossary|CMIS Query}}

<u><big>'''CMIS Repository'''</big></u>: {{#lst:Glossary|CMIS Repository}}

<u><big>'''CMIS'''</big></u>: {{#lst:Glossary|CMIS}}

<u><big>'''Content Type'''</big></u>: {{#lst:Glossary|Content Type}}

<u><big>'''Exchange'''</big></u>: {{#lst:Glossary|Exchange}}

<u><big>'''Execute'''</big></u>: {{#lst:Glossary|Execute}}

<u><big>'''Expressions'''</big></u>: {{#lst:Glossary|Expressions}}

<u><big>'''FTP'''</big></u>: {{#lst:Glossary|FTP}}

<u><big>'''Import Descendants'''</big></u>: {{#lst:Glossary|Import Descendants}}

<u><big>'''Import Provider'''</big></u>: {{#lst:Glossary|Import Provider}}

<u><big>'''Import Query Results'''</big></u>: {{#lst:Glossary|Import Query Results}}

<u><big>'''Node Tree'''</big></u>: {{#lst:Glossary|Node Tree}}

<u><big>'''NTFS'''</big></u>: {{#lst:Glossary|NTFS}}

<u><big>'''Repository'''</big></u>: {{#lst:Glossary|Repository}}

<u><big>'''Scope'''</big></u>: {{#lst:Glossary|Scope}}

<u><big>'''Service'''</big></u>: {{#lst:Glossary|Service}}

<u><big>'''SFTP'''</big></u>: {{#lst:Glossary|SFTP}}

<u><big>'''SharePoint'''</big></u>: {{#lst:Glossary|SharePoint}}

== About ==
'''''CMIS Queries''''' use clauses to retrieve content from a '''CMIS Repositories''' based on certain metadata property values, much like a SQL query uses clauses to retrieve data from a database based on column values.  In both cases, a <code>SELECT</code> clause is used to select content based on certain conditions determined by a <code>WHERE</code> clause 

In general the CMISQL statement takes the following form:

 <span style="color:blue">'''SELECT'''</span> * <span style="color:blue">'''FROM'''</span> <Content Type>
 <span style="color:blue">'''WHERE'''</span> <Conditions>
 <span style="color:blue">'''ORDER BY'''</span> <Sort Criteria>


For example, if you wanted to retrieve all email messages in your Inbox folder over an Exchange '''CMIS Connection''' from a particular sender and order results by the date you received the email (oldest first), you could use the following query:

 <span style="color:blue">'''SELECT'''</span> * <span style="color:blue">'''FROM'''</span> Message
 <span style="color:blue">'''WHERE'''</span> (Sender '''LIKE''' '%user@example.com%' '''AND'''  <span style="color:blue">IN_FOLDER</span>('/Inbox'))
 <span style="color:blue">'''ORDER BY'''</span> DateTimeReceived <span style="color:blue">ASC</span>


Next, let's break down this query further and look at what each clause is doing.

=== Clause Information ===
==== SELECT ====
 '''SELECT *''' ''FROM Message''
 ''WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox'))''
 ''ORDER BY DateTimeReceived ASC''

The <code>SELECT</code> clause defines a set of property values to be returned with each query result.  "Property values" refers to the available metadata properties for the object type selected for the <code>From</code> statement.  For example, when querying email messages using in an Exchange '''CMIS Repository''', there are metadata properties for the sender, the subject line, the date the email was sent, and more.  All these metadata properties are part and parcel to the email message file itself.

From a practical standpoint, your '''''CMIS Query''''' is ultimately returning ''documents''.  Files in a digital storage location.  You aren't retrieving individual metadata values, rather the whole file with all its various metadata included.  To that end you will use the <code>*</code> character to select ''all'' property values, when crafting a CMISQL query.

You will always start your '''''CMIS Query''''' with <code>SELECT *</code> when using a '''''CMIS Query''''' to import documents.

{|class="fyi-box"
|
'''FYI'''
|
When would you use something besides <code>SELECT *</code>?  When would you construct a CMISQL query, such as <code>SELECT Property_A, Property_B, Property_C</code> and so on?

The answer is '''''CMIS Lookups'''''.  A '''''CMIS Lookup''''' operates much like a database lookup.  Instead of querying a database table's columns for data, a '''''CMIS Query''''' queries the metadata properties of documents in a storage location.
* Technically, a '''''CMIS Query''''' is used in that case as well.  However, '''''CMIS Queries''''' are much more commonly used to retrieve documents from a storage location upon import, which is the focus of this article.
* For more information on '''''CMIS Lookups''''' please visit the [[CMIS Lookup]] article.
|}

==== FROM ====
 ''SELECT *'' '''FROM Message'''
 ''WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox'))''
 ''ORDER BY DateTimeReceived ASC''

The <code>FROM</code> clause determines what type of '''content''' you're searching for, such as a digital file or a folder and their metadata properties.
* The type of content you select determines what metadata properties you have access to when building the conditional logic in your query's <code>WHERE</code> clause.  

Depending on the '''CMIS Connection''' type, you will have different options to select from.  Some have simple "Document" and "Folder" content types, representing documents and folders respectively.  Some have a multitude of content types, each with metadata properties specific to the '''CMIS Binding'''.
* For example, the '''''Exchange''''' binding's '''Message''' content type has property values relating to email messages, such as '''''Subject''''', '''''Sender''''' and '''''DateTimeSent'''''.
* For example, The '''''SharePoint''''' binding's content types can be Document Libraries, giving you access to custom metadata properties you've created on the SharePoint site by adding custom columns to the Document Library.

In our example, we wanted to query email messages in an '''''Exchange''''' repository.  So, we selected the '''Message''' type by adding <code>FROM Message</code> to our query.

==== WHERE ====
 ''SELECT * FROM Message''
 '''WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox'))'''
 ''ORDER BY DateTimeReceived ASC''

The <code>WHERE</code> clause defines what search conditions must be met for a document to be retrieved.  Each condition is defined by a "predicate".  You can use any metadata available to the content type (as determined by the <code>FROM</code> clause) in your query conditions.  
* Multiple conditions/predicates can be joined with the <code>AND</code> or <code>OR</code> operators. 
* You can manipulate the logical order of operations by using nested parenthesis. 
* You can logically negate predicates using the <code>NOT</code> operator.

===== Search Predicates =====
The following is a list of predicates:

{|cellspacing="5" cellpadding="10"
|'''Predicate'''||'''Description'''||'''Example'''
|-style="background-color:#ddf5f5"
|'''''Comparison Predicate'''''
|
Specifies a condition for an individual property using comparisons, such as "equals to" <code>=</code> or "less than" <code><</code> or "greater than" <code>></code>.
* The <code>LIKE</code> and <code>IS</code> operators are also available for certain properties.
|<code>DateTimeCreated < '12/31/2007'</code>
|-style="background-color:#ddf5f5"
|'''''In Predicate'''''||Specifies a list of allowed values for a property.  This list is separated by commas.||<code>FileExtension IN ('.pdf', '.docx', '.xlsx')</code>
|-style="background-color:#ddf5f5"
|'''''Contains Predicate'''''
|Specifies a full-text query.
* You can use <code>AND</code>, <code>OR</code> and <code>NOT</code> operators when crafting search parameters.
* Please note only external platforms that support full text searching are compatible with with the '''''Contains Predicate'''''.
|<code>CONTAINS('mortgage AND payment AND NOT vehicle')</code>
|-style="background-color:#ddf5f5"
|'''''Scope Predicate'''''||Restricts the search scope to a specific folder (<code>IN_FOLDER</code>) or a specific folder including its subfolders(<code>IN_TREE</code>)||<code>IN_FOLDER('/Documents/Grooper')</code>
|-style="background-color:#ddf5f5"
|'''''Predicate List'''''||Defines a collection of predicates to be evaluated as a group using a logical operator (<code>AND</code> or <code>OR</code>).  Allows multiple search conditions in a single CMISQL query.||<code>(Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox/'))</code>
|}
* Note: Not every property type may be able to utilize every predicate/operators within a predicate.  For example, the '''''Subject''''' property on the '''''Exchange''''' binding cannot use the <code>=</code> operator when crafting a '''''Comparison Predicate'''''.
* Note: The <code>NOT</code> operator cannot be used with the <code>IN_FOLDER</code> or <code>IN_TREE</code> '''''Scope Predicate'''''.

In our example, we used a '''''Predicate List''''' that collected two predicates:  the '''''Comparison Predicate''''' and '''''Scope Predicate'''''.   This queried email messages from certain sender (<code>Sender LIKE '%user@example.com%'</code>) and in our inbox folder (<code>IN_FOLDER('/Inbox/')</code>)  Since we wanted to filter content by multiple criteria, the two conditions were joined with the <code>AND</code> operator.

==== ORDER BY ====
 ''SELECT *'' ''FROM Message''
 ''WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox'))''
 '''ORDER BY DateTimeReceived ASC'''</code>

The <code>ORDER BY</code> clause is an optional clause which allows you to specify the order in which results are returned.  You can sort by multiple properties using a comma separated list.  Each property name may be followed by <code>ASC</code> or <code>DESC</code> to indicate ascending or descending sort direction.
* The default sort direction is ascending.  However, it is still considered best practice to include the <code>ASC</code> tag to properties you wish to sort in ascending order.

In our example, we wanted to sort our incoming messages by oldest received first.

== CMISQL Query Editor ==
If you are not familiar with the CMISQL syntax (or even SQL), you may find it easier to use the '''''CMIS Query Editor''''', instead of writing the full query yourself.  This allows you to configure the CMISQL query using a series of property grids and menu selections instead of writing out the query from scratch.

The '''''CMIS Query Editor''''' is accessible in two locations:
# When configuring the '''''Import Query Results''''' Import Provider's '''''CMIS Query''''' property.
# Using a '''CMIS Repository's''' "Search Repository" tab.

{|class="attn-box"
|
&#9888;
|
Only certain external storage platforms are currently queryable with a '''''CMIS Query'''''.  The following '''''CMIS Binding''''' sources '''''cannot''''' be queried currently.

* '''''FTP'''''
* '''''SFTP'''''
* '''''NTFS''''' (If the folder path is not indexed by the Windows Search service and/or Windows Search is not running on the storage server)

As such, these bindings are '''''not''''' suitable for the '''''Import Query Results''''' provider (You should instead use '''''Import Descendants''''').  Furthermore, you will not have access to the "Search Repository" tab for these bindings when selecting a '''CMIS Repository'''.
<br>
Again, the exception to this is '''''NTFS'''''. If the folder path IS indexed in Windows and the Windows Search service IS running, then you CAN use the "Search Repository" tab and '''''CMIS Queries'''''.
|}

=== How to Access the CMIS Query Editor ===
<tabs style="margin:20px">
<tab name="Option 1: Import Query Results - CMIS Query" style="margin:20px">

=== Option 1: Import Query Results > CMIS Query ===
Most commonly, you will use a CMISQL query to import documents using the '''''Import Query Results''''' provider.

{|class="fyi-box"
|
'''FYI'''
|
The '''''Import Query Results''''' provider is one of two '''''Import Providers''''' that use a '''CMIS Connection''' to bring document content into Grooper.  If you need more information on '''''Import Query Results''''', please visit the [[CMIS Import]] article.
|}

{|cellpadding=10 cellspacing=5
|valign=top style="width:40%"|
<br>
# When configuring the import settings, you will first select which '''CMIS Repository''' you want to query by selecting one using the '''''Repository''''' property.
# Then, select the '''''CMIS Query''''' property.
#* Note:  Some '''CMIS Bindings''' are ''not'' CMISQL queryable (For example, the '''''FTP''''' and '''''SFTP''''' CMIS Bindings).  If the '''CMIS Repository''' is not queryable, you will ''not'' see this '''''CMIS Query''''' property.
# Press the ellipsis button at the end of the property to bring up the '''''CMIS Query Editor'''''.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_01.png]]
|-
|valign=top|
<br>
This is the '''''CMIS Query Editor'''''.  Using this interface you can craft a CMISQL query in one of two ways:

# You can use this property grid to configure the various clauses of the query statement.
# You can free-type the query in this text editor.


We will demonstrate how to use this query editor in the [[#How to Use the CMIS Query Editor|next section]]
|valign=top|
[[File:2023_CMIS-Query_02_How-To_02.png]]
|}
</tab>
<tab name="Option 2: The Search Repository Tab" style="margin:20px">

=== Option 2: The Search Repository Tab ===
You can also use the '''''CMIS Query Editor''''' when searching for documents in a '''CMIS Repository''' using the "Search Repository" tab.

{|cellpadding=10 cellspacing=5
|valign=top style="width:40%"|
<br>
# Select the '''CMIS Repository''' you want to query in the Node Tree.
# Navigate to the "Search Repository" tab.
#* Note:  Some '''CMIS Bindings''' are ''not'' CMISQL queryable (For example, the '''''FTP''''' and '''''SFTP''''' CMIS Bindings).  If the '''CMIS Repository''' is not queryable, you will ''not'' see this "Search Repository" tab.
# You can use this property grid to configure the various clauses of the query statement.
# You can free-type the query in this text editor.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_03.png]]
|}


We will demonstrate how to use this query editor in the [[#How to Use the CMIS Query Editor|next section]]
</tab>
</tabs>

=== How to Use the CMIS Query Editor ===
Next, we're going to illustrate how to use the '''''CMIS Query Editor''''' in order to filter email messages based on certain criteria.  The '''''CMIS Query Editor's''''' interface allows you to configure the CMISQL query based on available metadata to the '''''CMIS Binding'''''.

We will use the '''''Exchange''''' binding, which has a selection of queryable metadata for email messages, such as the email's subject and date the message was received.  Our query will import email messages based on the following conditions:
# Only messages are to be imported (as opposed to other '''''Exchange''''' content, such as appointments or tasks).
# Only messages in a certain folder will be imported.
# Only messages containing specific text search terms will be imported.
# Only messages from a certain sender will be imported.
# Only messages that have not been read yet will be imported.

This will get us through the basics of building a CMISQL query using the '''''CMIS Query Editor'''''.

<tabs style="margin:20px">
<tab name="The SELECT and FROM Clauses" style="margin:20px">
{|cellpadding=10 cellspacing=5
|valign=top style="width:40%"|
=== The SELECT and FROM Clauses ===
The property grid is divided into the various clauses that form the CMISQL query.  Adjusting these properties will adjust the CMISQL query.

# The '''''Select''''' property will adjust the <code>SELECT</code> clause.
#* By default this property is set to ''*''.
#* This forms the start of the query <code>SELECT *</code>
# The '''''From''''' property will adjust the <code>FROM</code> clause.
#* Each '''''CMIS Binding''''' will default to a different content type.  Generally, the default content type corresponds to a "document" in the storage location.
#* The '''''Exchange''''' binding's default is ''Item''.  However, we actually want to change this.  An "item" is too generic.  It refers to message files, appointment files, task files, and note files in an Exchange inbox.  
#** We want to import email messages, and define our query conditions to filter out emails using metadata specifically accessible to those types of files. We need to use the ''Message'' content type to do this properly.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_04.png]]
|-
|valign=top|
=== Changing Content Types in the FROM Clause ===

We will use the '''''CMIS Query Editor's''''' property panel to edit our query going forward.

# Since we want to adjust the content type in our <code>FROM</code> clause, we will select the '''''From''''' property.
# Using the dropdown menu, we've selected ''Message''.
#* This will query specifically message files over our Exchange connection, and give use access to their metadata for our querying conditions.
# This automatically updates the full text of our CMISQL query.
#* <code>SELECT * FROM Item</code> changed to <code>SELECT * FROM Message</code>



As we use the property grid to further configure our query, the query's text will update automatically.
* This gives you a method of editing the CMISQL query without free-typing the query.
* However, you certainly can type out the full query text if you prefer ''not'' to use the property grid to enter the CMISQL query.
** You can also go back and forth between the property grid and the text editor.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_05.png]]
|}
</tab>
<tab name="The WHERE Clause" style="margin:20px">
{|cellpadding=10 cellspacing=5
|valign=top style="width:40%"|
=== The WHERE Clause ===

Next, we will use the '''''Where''''' property to configure the various filter conditions we've set for ourselves.

# Select the '''''Where''''' property.
# Using the dropdown list, select the search predicate you wish to use.
#* If you are filtering based of multiple criteria. you will '''''ALWAYS''''' choose ''Predicate Collection''.
#* We need to filter based on the values of multiple metadata properties, ''and'' we need to use both a '''''Comparison Predicate''''' and a '''''Scope Predicate'''''.  So, we have selected '''''Predicate Collection'''''.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_06.png]]
|-
|valign=top|
=== Using Predicate Collection for Multiple Search Conditions ===
# Expand the '''''Where''''' sub-properties to add your query criteria.
# The default '''''Logical Operator''''' is ''AND''.
#* This is appropriate for our situation, as we want to use the ''AND'' operator.  ALL conditions must be met in our case.
#* But be aware you also have access to the ''OR'' operator.
# Next, we will add our various query conditions using the '''''Expressions''''' property.
# Press the ellipsis button at the end to bring up the '''''Where Predicate Expressions''''' collection editor.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_07.png]]
|}
</tab>
<tab name="Adding Search Predicates" style="margin:20px">
{|cellpadding=10 cellspacing=5
|valign=top style="width:40%"|
=== Adding Search Predicates ===
Now that we're in the '''''Where Predicate Expressions''''' collection editor, we can start adding and configuring our search conditions.

# Press the ''Add'' button to add a predicate to the list.
# We will start by adding a '''''Scope Predicate''''' to restrict the search scope to a specific folder.
|
[[File:2023_CMIS-Query_02_How-To_08.png]]
|-
|valign=top|
=== The Scope Predicate ===

The '''''Scope Predicate''''' allows you to choose a folder from which you want to retrieve documents.

# Select the '''''Search Scope''''' property and press the ellipsis button at the end.
# This will bring up a folder browser, representing your connected '''CMIS Repository's''' hierarchical folder structure.
# We're going to restrict our scope to this subfolder named "Wiki" in the connected email account.
# Press ''OK'' to finalize your selection.

{|class="fyi-box"
|
'''FYI'''
|
If '''''Include Subfolders''''' is ''False'', the <code>IN_FOLDER()</code> predicate will be used.
:&bull; This will restrict the query's search to the single selected folder in the folder hierarchy.

If '''''Include Subfolders''''' is ''True'', the <code>IN_TREE()</code> predicate will be used.
:&bull; This will restrict the query's search to the selected folder ''and'' search any of its subfolders in the folder hierarchy.
|}
|valign=top|
[[File:2023_CMIS-Query_02_How-To_09.png]]
|-
|valign=top|
=== The Contains Predicate ===

If the storage location your '''CMIS Repository''' is connected to supports full text searching, you can use the '''''Contains Predicate''''' to query documents based on specific text search terms.

For example, we want to retrieve email messages pertaining to "Wiki Vitals", a specific kind of report generated nightly about the Grooper wiki and sent to my email.

To add a '''''Contains Predicate''''':
# Press the ''Add'' button and select '''''Contains Predicate'''''
# This adds a new '''''Contains Predicate''''' to our list of predicates
# Using the '''''Search String''''' property, enter the text you would like to search for.
#* In our case, only messages with the text ''Wiki Vitals'' somewhere in the subject line or email body or otherwise in the message's metadata will be retrieved.
#* Notice Grooper automatically generates the property syntax for the query, <code>CONTAINS('Wiki AND Vitals')</code>.

{|class="attn-box"
|
&#9888;
|
Please note only external platforms that support full text searching are compatible with with the '''''Contains Predicate.
|}
|valgin=top|
[[File:2023_CMIS-Query_02_How-To_10.png]]
|-
|valgin=top|
=== The NOT Operator ===

All predicates can be logically negated using the <code>NOT</code> operator.  For example, most of these "Wiki Vitals" reports are daily reports.  These are the emails I want to process.  However, once a month, there is a monthly report.  I DO NOT want to process those documents.

The only way I know it's a monthly report is the term "monthly report" is listed in the body of the email.  I can use the '''''Contains Predicate''''' and the <code>NOT</code> operator to exempt these monthly reports from the query.

To logically negate a predicate:
# Add the predicate using the ''Add'' button.
# We've added another '''''Contains Predicate'''''.
# Configure the predicate's search parameters.
#* What do we ''not'' want to retrieve in this case?  Messages with the text ''Monthly Report'' somewhere in their body.  So, we've configured the '''''Search String''''' property to search for that term.
# Change the '''''Not''''' property to ''True''.
#* This will negate the predicate's logic.  If the email contains the search term, it will ''not'' be retrieved.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_11.png]]
|-
|valign=top|
=== The Comparison Predicate ===

The '''''Comparison Predicate''''' specifies search criteria based on a document's metadata property values, using comparison operators such as <code>></code>, <</code> or <code>=</code>.

For example, we will use a '''''Comparison Predicate''''' to only import emails that have not been read yet, whose "IsRead" property value equals "False".

To add a '''''Comparison Predicate'''':
# Press the ''Add'' button and select '''''Comparison Predicate'''''
# This adds a new '''''Comparison Predicate''''' to our list of predicates
# Using the '''''Property Name''''' property, select a property from a list of the documents' available metadata properties.
#* What metadata properties are available will be determined by the '''CMIS Repository's''' binding/connection type and the content type selected in the <code>FROM</code> clause.
#* We've selected the ''IsRead'' property.
# Using the '''''Comparison Operator''''' select which operator you want to use for comparison.
#* Depending on the value's type (string, decimal, Boolean, etc.) different comparison operators may be available.
#* Here, we are checking for equivalency using the <code>=</code> operator.
# Using the '''''Search Value''''' property, enter the control variable you want to use for comparision.
#* In our case, we want to check if these emails have not been read, indicated by the email message's "IsRead" property being ''False''.
|
[[File:2023_CMIS-Query_02_How-To_12.png]]
|-
|valign=top|
=== The LIKE Operator ===

The <code>LIKE</code> operator is used to match substrings of string values.  In many cases, it must be used instead of the <code>=</code> when you're comparing string variables.

For example, if we want to use a '''''Comparison Predicate''''' to filter messages based on the "Sender" (a string value), we will need to use the <code>LIKE</code> variable to do so.  Furthermore, we will also need to encase the search term in the <code>%</code> wildcard symbol.

# Here, we've added a new '''''Comparison Predicate''''' to only retrieve messages sent by a specific email address.
# We've slected ''Sender'' for the '''''Property Name'''''
# This property must use the <code>LIKE</code> '''''Comparison Operator'''''.
# For the '''''Search Value''''' property we've entered ''%cdearner@bisok.com%''.
#* Effectively, we just enter the email address we want to search for (cdearner@bisok.com) with percent sign wildcards at the front and end.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_13.png]]
|-
|colspan=2|
{|class="fyi-box"
|
'''FYI'''
|
The <code>LIKE</code> operator specifies that a property value must contain a substring. It takes the general form <code>PropertyName LIKE 'Expression'</code>, where <code>PropertyName</code> is the query name of a string property, and <code>Expression</code> is a string literal using the <code>%</code> and <code>_</code> characters as wildcards. The <code>LIKE</code> operator is not case sensitive. 
:&bull; The <code>%</code> wildcard substitutes for zero or more characters. 
:&bull; The <code>_</code> wildcard substitutes for exactly one character. 
:&bull; A <code>LIKE</code> expression with no wildcards is equivalent to using the <code>=</code> operator. 
:&bull; For example, the expression <code>'grooper%'</code> will match cases where the property value begins with "grooper", while <code>'%grooper'</code> will match cases where the property value ends with "grooper". The expression <code>'grooper%review'</code> will match cases where the property value begins with "grooper" and ends with "review".

Some '''CMIS Bindings''' have limited support for <code>LIKE</code> expressions, and will raise errors when unsupported features are used. Examples of such issues are as follows:
:&bull; Some bindings '''only''' support the <code>%</code> wildcard at the beginning and end of the expression (i.e. <code>'%grooper%'</code>).
:&bull; Some bindings '''do not''' support the "ends with" form of <code>LIKE</code> (i.e. <code>'%grooper'</code>).
:&bull; Some bindings '''do not''' support the <code>_</code> wildcard at all.
|}
|-
|valign=top|
=== Finish Adding Predicates ===

# When you have finished adding predicates to the collection list, press the ''OK'' button.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_14.png]]
|-
|valign=top|
<br>
#<li value=2> When finished, the search predicates will form the conditional parameters of the query's <code>WHERE</code> clause.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_15.png]]
|}

We ''could'' be done at this point.  We have the three basic parts of a '''''CMIS Query'''''.

# The query starts with <code>SELECT *</code>
# The <code>FROM</code> clause determines which content (documents/folders and their metadata properties) we're querying from the '''CMIS Repository'''/storage location.
# The <code>WHERE</code> clause defines the query's search conditions.

Optionally, you may want to ''order'' the documents Grooper retrieves.  We will do that next, with the <code>ORDER BY</code> clause.
</tab>
<tab name="The ORDER BY Clause (and Testing the Query)" style=margin:20px">
=== The ORDER BY Clause ===
The <code>ORDER BY</code> clause is an optional CMISQL querying clause used to change the sort order of the documents Grooper retrieves.  You can sort by any metadata property available.

For example, we may want to sort by the date the email message was sent.  And specifically we want the most recent email first in the list and the last email last in the list.  We're going to need an <code>ORDER BY</code> clause to do this.
{|cellpadding=10 cellspacing=5
|valign=top style="width:40%"|
<br>
To add an <code>ORDER BY</code> clause:
# Select the '''''Order By''''' property, and press the ellipsis button at the end.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_16.png]]
|-
|valign=top|
# <li value=2>This brings up the '''''Order By''''' collection editor.</li>
# Press ''Add'' to add a new sort element.
# Using the '''''Property Name'''''' property, select which document metadata property you want to sort by.
#* In our case, we've chosen the email messages ''DateTimeSent'' property, storing what date and time the email was sent.
# Using the '''''Sort Direction''''' property, select which direction you want to sort by.
#* Choose ''ASC'' to sort in ascending order.
#* Choose ''DESC'' to sort in descending order.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_17.png]]
|-
|valign=top|
=== Testing the Query ===

# With this <code>ORDER BY</code> clause added, our query is now complete.
# This message indicates the query is valid.  There is nothing wrong with our syntax.
# You can test the query by pressing the ''Execute Query'' button.
# The query's results will appear in the '''''Query Results''''' pannel
# If you double-click on an item in the list, it will appear in the '''''Document Preview''''' panel.
|valign=top|
[[File:2023_CMIS-Query_02_How-To_18.png]]
|}
</tab>
</tabs>

== Unsupported Query Configurations ==

=== NTFS ===
{{#lst:2023:NTFS (CMIS Connection Type)|ntfs_query_limitations}}

=== SharePoint ===
{{#lst:2023:SharePoint (CMIS Connection Type)|sharepoint_query_limitations}}

=== Box ===
{{#lst:2023:Box (CMIS Connection Type)|box_query_limitations}}

=== Exchange ===
{{#lst:2023:Exchange (CMIS Binding)|exchange_query_limitations}}