⌂ SELECT
Clauses
- SELECT
- FROM
- IN
- WHERE
- WITH
- GROUP BY
- ORDER BY
- LIMIT

cQL SELECT Statement

A SELECT statement queries and loads repository objects by content type and field. A cQL SELECT statement is similar to an SQL SELECT statement, with the exceptions described in the cQL Overview.

A SELECT statement is made up of zero or more of the following clauses. If multiple clauses are specified, they must appear in the order below.

SELECT - Specifies which fields and calculated values to return for each resulting object or row.
FROM - Restricts results to objects of one or more content type(s).
IN - Restricts results to objects within one or more folders.
WHERE - Filter results to objects that match certain criteria.
WITH - Restrict and/or filter results to specific dimension values.
AS OF - Get results for a point in time.
GROUP BY - Group results into aggregate objects or rows.
HAVING - Filter grouped, by GROUP BY, results that match certain criteria.
ORDER BY - Sort results by one or more fields.
LIMIT - Restrict results to a specific offset and/or length of the complete set of results.

SELECT

The SELECT clause is identical to the SQL equivalent, except dot-notation is supported to reference deep content without JOINs. For example:

SELECT Name, Phone.Number FROM Contact

Fields referenced in the SELECT clause are loaded and cached from the database as query results are retrieved. Other fields are lazy loaded as they are referenced in code. So, it is an important optimization to select the fields you plan to reference, to avoid additional subsequent queries.

The '*' wild card is supported. However, it refers to immediate fields. Both data type and content type field relationships will be loaded. However, the fields of the objects in the relationship will not be loaded, unless explicitly selected.

# load all fields, including Phones collection, but not Phone fields
SELECT * FROM Contact

# load Name field and Phones collection, but not Phone fields
SELECT Name, Phones FROM Contact

# load all fields, including Phones collection and Phone fields
SELECT *, Phones.* FROM Contact

# load Name field, Phones collection and Phone fields
SELECT Name, Phones.* FROM Contact

SQL functions are supported, including aggregate functions. Like SQL, aggregate functions used without a GROUP BY clause will group all results into one.

SELECT Name, GROUP_CONCAT(Phones.Number SEPARATOR ', ') AS PhoneNumers
FROM Contact GROUP BY OID

Aliases

Aliases are also supported. When an alias is identical to an object field, the field value is overridden with the alias value. Overridden values will be saved if the object is subsequently saved. When an alias is not identical to an object field, a temporary object field is created. Temporary object fields are not saved if the object is saved.

SELECT Name, TIMESTAMPDIFF(YEAR, DOB, NOW()) AS Age FROM Contact

FROM

The FROM clause is similar to the SQL equivalent on the surface, and in most simple queries they are used exactly the same way.

SELECT ID, UUID, Name FROM Contact

However, there are some significant differences:

Dot-Notation Instead of Joins

First, JOINs (of any type) are not allowed. Instead, dot-notation is supported in SELECT, WHERE, GROUP BY and ORDER BY clauses to reference deep content without JOINs. For example:

SELECT Name, Phones.Name FROM Contact

Attribute Reference '@'

Content that references content in the FROM clause can be queried with an attribute reference. This is useful to query or filter out content based on references to it from other content.

Attribute references take the form @(ReferenceField)ContentType.SubField where (ReferenceField) and SubField are optional.

The following example will get all Contacts with a 'Family' Tag.

FROM Contact WHERE @Tag='Family'

For more information and examples, see COMAND Attributes.

Multiple Content Types

While JOINs are not neccessary, it is possible and common to list multiple comma-separated content types in the FROM clause. Unlike SQL though, comma's do not imply an INNER JOIN. Instead, it is similar to a UNION, in that the query will be applied to all listed content types as if their objects were in the same table. That is, multiple content types can be treated the same way to query on similar fields. Fields that are not shared across content types are considered to have a value of NULL. This feature is similar to a NoSQL-style query.

For example, the following query will get the name of all contacts and employees born before Jan 1, 2000. This assums both Contact and Employee have a Name and DOB field.

SELECT Name FROM Contact, Employee WHERE DOB < '2000-01-01'

If Employee did not have a DOB field, no Employees would be selected because DOB would be treated like it had a value of NULL, so the comparison would fail.

Extends Suffix '+'

A content type identifier can also be followed immediately by a plus symbol to include the content type itself, as well as any content types that extend it. The following example would be equivelent to the previous example if Employee were the only content type to extend Contact.

SELECT Name FROM Contact+ WHERE DOB < '2000-01-01'

FROM Object

A query from the Object content type (ie. "FROM Object") is a special case. It will return all objects, including objects based on content types that extend object. This is useful for querying everything in a small database without the overhead of "FROM Object+". A "FROM Object" query can not reference fields that are not in the object content type though. In order to reference fields in the individual content types, "FROM Object+" is required, which can be very resource intensive on large repositories that use the SQL storage engine.

Note to SQL storage engine developer: A future version of the query engine could potentially optimize "FROM Object+" queries by combining the way "FROM Object" and "FROM Object+" work, but only performing a UNION for content types that have one of the referenced fields, and grabbing all other content type objects (with Type.ID IN (all else)) in the main object query with NULL field values.

Content Type Interfaces (Polymorphic Queries)

The following feature is not yet implemented, but should be part of API version 1.0.

Finally, content type names can reference interfaces (abstract content types) instead of standard content types. When an interface is referenced, any content type that implements the interface will be queried. This is similar to the Extends Suffix '+', except content types that do not extend each other can be queried as a group. This concept is called polymorphism, or more specifically polymorphic queries.

In addition to access to their common fields, non-common fields can also included in the query. If the field is not available for a content type, its value will be considered NULL.

The example below assumes a content type interface Article has Title and Date fields. If News, Blog and PressRelease content types all implement the Article interface, objects of those content types could be included in the results.

SELECT Title FROM Article WHERE Date < '2000-01-01'

IN

An IN clause can use cPath to filter results to objects in specific folders in the folder hierarchy. For example, the following query would limit results to contacts in the Contacts folder.

SELECT Name FROM Contact IN /Contacts WHERE DOB < '2000-01-01'

Zero or more of the following keywords (not case sensitive) can follow the IN clause to produce a collection of related folders, each preceded by "OR ".

OR RELATED - Related folders (up to top-level folder, just above root, and all of its sub-folders recursively, in closes first order.
OR PARENTS - Parent folders up to the top-level folder, just above root.
OR CHILDREN - sub-folders recursively.

The query below will produce the same results as the previous example. RELATED and PARENTS are more useful because they are not as easily expressed in cPath.

SELECT Name FROM Contact IN /Contacts OR CHILDREN WHERE DOB < '2000-01-01'

Multiple IN clauses, or comma-separated folders (with optional "OR" modifiers) can be specified to match on any of a number of folders and folder hierarchies.

FROM Contact IN /Contacts OR CHILDREN, /My/Stuff, 123 OR RELATED

WHERE

The WHERE clause is used to extract only those records that fulfill a specified criteria. It is almost identical to an SQL WHERE clause, except dot-notation can be used to reference other objects through relationship fields.

Supported features and syntax are listed below, in order of operation/precedence. All are equivalent to SQL, unless otherwise specified.

Unary Operators: NOT (can also use !)
Binary Operators: *, / %, +, -
Comparisons: =, ==, !=, <, >, <=, >=, LIKE, ILIKE, RLIKE, BETWEEN, IN.
Functions: ABS, ISNULL, LOWER, REVERSE, UPPER, etc.
Aggregate Functions: COUNT, MIN, MAX, SUM, AVG. When query results are returned as objects, COUNT is a bit different than the SQL equivalent because it can accept a content type field (a field that references an object or collection) as the parameter. COUNT(*) and COUNT() are both allowed and will provide the number of objects in the top-level result collection. COUNT(Field.*) and COUNT(Field) are both allowed and will provide the total number of objects in the referenced field, potentially across many child and intermediary objects, for the top-level collection objects.
Logic: AND (can also use &&) and OR (can also use ||)
Parenthesis Grouping - Any number and depth of parenthesis can group comparisons and logic.

SELECT * FROM Contact WHERE
DOB BETWEEN '2000-01-01' AND '2013-01-01' AND
(Address.City='New York' AND Address.State='NY') OR
(Address.City='Los Angeles' AND Address.State='CA')

WITH

The WITH clause is used to restrict results to a specific dimension value, with optional fallback(s). It will only apply to dimensional content and have no effect on other content.

For example, to restrict results to only content with an English version:

SELECT Name FROM Contact WHERE DOB < '2000-01-01' WITH EN

To prefer content with an English version, but fall back to the "default" (initially defined for each content record) language for all other content:

SELECT Name FROM Contact WHERE DOB < '2000-01-01' WITH EN,*

To prefer content with an English version, but fall back to German and then the "default" language for all other content:

SELECT Name FROM Contact WHERE DOB < '2000-01-01' WITH EN,DE,*

To prefer content with an English version, but fall back to German, and exclude all other content:

SELECT Name FROM Contact WHERE DOB < '2000-01-01' WITH EN,DE

AS OF

Version Control and Snapshots make it possible to retain previous versions of content. The AS OF clause makes it possible to query and retrieve retained versions of content based on a timestamp (e.g. '2020-04-30 13:03:23'), revision number (e.g. 123456), workflow state (Active, Draft, Working), or relative format (e.g. now, yesterday, '2 hours ago').

SELECT Name FROM Contact WHERE OID = 123 AS OF yesterday

If the value contains spaces, it must be quoted.

SELECT Name FROM Contact WHERE OID = 123 AS OF '2 minutes ago'

Timestamps can be in database format.

SELECT Name FROM Contact WHERE OID = 123 AS OF '2020-04-30 13:03:23'

GROUP BY

Same as SQL, except supports dot-notation.

SELECT Age, COUNT() AS Total FROM Contact GROUP BY Age ORDER BY Age

ORDER BY

Same as SQL, except supports dot-notation.

SELECT *, Address.* FROM Contact ORDER BY Address.State, DOB DESC

LIMIT

Same as SQL, will limit the number of results.

If a single number is provided, it will be used to limit the number of results, starting from the first result.
If two comma-separated numbers are specified, the first is used as a zero-based offset and the second is the maximum number of results from that offset.

SELECT * FROM Contact ORDER BY Address.State, DOB DESC LIMIT 10,5