cQL vs SQL

cQL is very similar to SQL, so it is easy for anyone familiar with SQL to pick up. However, COMAND repositories and collections contain object data, not just relational data. So, a few SQL features that are not appropriate are missing (JOINs), and a few that do not exist in SQL have been added (hierarchy and dimensions).

For example, the following cQL is identical to SQL. It gets Contacts who are older than 30.

SELECT * FROM Contact WHERE Age > 30

Optional Clauses

A simple difference between cQL and SQL queries is that all clauses are optional in cQL (SELECT and FROM are required in SQL). The following example omits the SELECT clause.

FROM Contact WHERE Age > 30

Dot-Notation Fields

Content relationships are known in a repository, so JOINs are simplified to dot-notation.

SELECT Name, Phone.Number FROM Contact WHERE Address.State = 'NY'

For comparison, a similar SQL query on a relational database would look something like the following.

SELECT contact.name, phone.number FROM contact
JOIN address ON (address.contact_id=contact.id)
LEFT JOIN phone ON (phone.contact_id=contact.id)
WHERE address.state = 'NY'

Attributes (@-Notation)

Attributes are "inbound references", or the inverse of dot-notation fields, which are "outbound references". In other words, content that references a Contact in a "FROM Contact" query are attributes of the contacts.

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 (that references the Contact).

FROM Contact WHERE @Tag='Family'

For more information and examples, see Query Attributes.

Hierarchy (IN)

Repository content can be organized into hierarchies, much like files in a file system. The IN clause provides a way to filter results based on the hierarchy with cPath.

SELECT * FROM Contact IN /Users/John// WHERE Age > 30

Simple hierarchies like this can be accomplished with SQL using the nested set model. In fact, COMAND internally handles hierarchies that way for a relational database storage engine. However, the IN clause is more elegant and readable than the SQL equivalent, especially for an IN clause with a more complex cPath.

Dimensions (WITH)

Repository content may be multi-dimensional. For example, content can be translated into multiple languages resulting in a single content record with multiple variations. A query can limit results by dimension, and even fall back to preferred variations. The WITH clause provides a way to do this.

The following example will get Web Pages in French, and fall back to English if French does not exist.

SELECT * FROM WebPage WITH FR, EN

Collection Results

While cQL is similar to SQL as a query language, a key difference is how query results can be returned. In SQL, when tables with "to many" relationships are joined, the row data to the left of the join is duplicated for each joined row. cQL can return objects. To access the objects in a collection, simply iterate through the collection field in the result object. In other words, result fields (unlike SQL columns) can be collections or objects themselves, not just data type values. This is a powerful feature and important difference that should be kept in mind. It affects the results of queries that use aggregation. See Row Sets below for SQL-style results.

Row Set Results

If relational database-style results are desired, the API App and API can return a row set instead. Row sets will include redundant rows from tables that join to multiple rows in another table ("to many" relationships).