│ Grooper Help - Version 25.0
25.0.0017 2,127
  • Overview
  • Help Status

CMISQL Query

CMISQL Element Grooper.CMIS

Defines a CMISQL query that selects documents or folders from a CMIS Repository.

Remarks

A CMISQL Query is used to search for documents or folders in a CMIS Repository using a SQL-like syntax. CMISQL is based on a subset of the SQL-92 grammar, with extensions specific to CMIS. The query specifies the type of item to search for, the criteria for matching items, the order in which results are returned, and which properties are included in the results. All properties and content types referenced in a CMISQL Query must use their "query name".

The general form of a CMISQL statement is:

SELECT <Properties> FROM <Type> [WHERE <Conditions>] [ORDER BY <Sort>]

SELECT Clause

The SELECT clause determines which properties are returned with each result. It can be a comma-separated list of property names, or * to return all properties.

FROM Clause

The FROM clause specifies the type of object to search for. This should be the name of a queryable content type defined in the CMIS Repository. If the type is document-based, each result will be a CMIS Document; if folder-based, each result will be a CMIS Folder. The content type in the FROM clause defines which properties are available in the SELECT, WHERE, and ORDER BY clauses, and limits the search scope to objects of that type (including inherited types).

WHERE Clause

The optional WHERE clause defines one or more conditions that items must match to be included in the results. Multiple conditions can be joined with AND or OR, and grouped with parentheses to control order of operations. Supported predicate types include:

Predicate Type Description Example
Comparison Predicate Condition for an individual property invoice_date < '12/31/2007'
IN Predicate List of allowed values for a property FileExtension IN ('.pdf', '.docx', '.xlsx')
CONTAINS Predicate Full-text search CONTAINS('mortgage AND payment AND NOT vehicle')
Scope Predicate Restricts search to children/descendants of folder IN_FOLDER(/Inbox)
IN_TREE(/Inbox)

The NOT operator can be used to invert predicates or groups. Note that some CMIS Bindings may have limited support for NOT.

ORDER BY Clause

The optional ORDER BY clause specifies the sort order for results. It is a comma-separated list of property names, each optionally followed by ASC or DESC to indicate ascending or descending order. If no direction is specified, ascending is assumed.

CMISQL Query Examples

Example 1. Returns all invoices where the invoice amount is greater than 5000 and freight is less than 100, sorted by invoice amount in descending order.

SELECT * FROM INVOICES WHERE (invoice_amount>5000) AND (freight<100) ORDER BY invoice_amount DESC

Example 2. Returns specific properties for invoices with a particular invoice number.

SELECT po_number, invoice_no, invoice_date, freight, sales_tax, invoice_amount FROM INVOICES WHERE (invoice_no='123456789')

Example 3. Returns all files with a file extension of '.pdf' or '.docx', created after January 1, 2018, and containing both 'grooper' and 'cmis' in their content.

SELECT * FROM File WHERE FileExtension IN ('.pdf', '.docx') AND cmis:creationDate > '1/1/2018' AND CONTAINS('grooper AND cmis')

Example 4. Returns all messages where the subject contains 'grooper', sent after January 1, 2018, and containing 'training' in their content.

SELECT * FROM Message WHERE Subject LIKE '%grooper%' AND DateTimeSent > '1/1/2018' AND CONTAINS('training')

CMISQL Query objects are typically configured by specifying the SELECT, FROM, WHERE, and ORDER BY clauses using the corresponding properties. The class provides parsing, validation, and error reporting for CMISQL statements, and supports advanced features such as joins, secondary types, and filter mode for specialized queries.

For more information about CMISQL syntax and supported features, refer to the documentation for CMIS Repository, CMIS Document, CMIS Folder, and the various predicate types.

Properties

NameTypeDescription

See Also

Select ElementJoin ClauseAT_LEVEL PredicateComparison PredicateCONTAINS PredicatePredicate ListIN PredicateMATCHES PredicateScope PredicateORDER BY Element

Notification