Adaptive Work REST API Reference Guide

The Clarizen Query Language (CZQL) allows you to perform queries in an "SQL like" syntax. CZQL supports all the conditions and expression currently supported in the entityQuery API, including the aggregate queries and result grouping that are supported in the aggregateQuery API
The general syntax of CZQL is as follows

SELECT [@]field | groupingFunction [AS alias] | (Relation Query) 
FROM EntityType
WHERE Condition
GROUP BY groupByFields
ORDER BY orderByFields [ASC | DESC]
LIMIT rows OFFSET startFrom

The following samples cover most of the features available in CZQL. Please note that although the samples use UPPERCASE indicating reserved words for brevity, CZQL is case insensitive. Field names can be prefixed with '@' (e.g. @Name). This is recommended when the field name matches a reserved word.

Simple SELECT with condition on a date field

The example below finds the first 100 tasks (100 is the default limit) with a StartDate after Jan 1st 2014. The results includes the task name, the manager, and the start date. The result is then sorted by StartDate in descending order.

SELECT @Name, StartDate, Manager
FROM Task
WHERE StartDate>2014-01-01
ORDER BY StartDate DESC

Response:

{
 "entities": [
  {
   "id": "/Task/e22ca960-73bb-4cf6-b6e8-b638ed0ab1fd",
   "Name": "New Task",
   "StartDate": "2014-11-03T08:00:00.0000000",
   "Manager": {
    "id": "/User/9bcd7df3-4f6b-4272-944c-219b65b7862a"
   }
  },
...
{
   "id": "/Task/2e6d2860-cea2-4a22-b6fd-f4f971dc07d3",
   "Name": "task abb59568-01c8-47de-9081-10e858d4b5a3",
   "StartDate": "2014-10-03T08:00:00.0000000",
   "Manager": {
    "id": "/User/9bcd7df3-4f6b-4272-944c-219b65b7862a"
   }
  }
 ],
 "paging": {
  "from": 100,
  "limit": 100,
  "hasMore": true
 }
}

Note the following in the above example:

1. Dates should be entered in CZQL as literals in ISO8061 format. The following example are supported by CZQL

   2014-05-28
   2014-05-28T08:00
   2014-05-28T13:00:00
   2014-05-28T13:00:00Z
   2014-05-28T13:00:00+01:00

2. When requesting fields which reference other entities (Like Manager in the above example), The returned value is an object containing the ID of the referenced entity

SELECT with multiple conditions and different paging options

SELECT Name, StartDate, Manager
FROM Task
WHERE (StartDate>2014-01-01 AND DueDate<2014-02-28) OR
                                                    (StartDate<2014-01-01 AND State IN ('Active','Draft'))
LIMIT 500 /* Return 500 results instead of the default 100 */
OFFSET 150 /* Skip the first 150 results */

SELECT with nested fields

When selecting fields you can request the values of "sub-field" (or "nested fields") from fields which reference other entities. In the following example, we request the Name and MobilePhone nested fields from the Manager field.

SELECT Name, Manager.Name, Manager.MobilePhone, Parent.Parent.Name
FROM Task

Response:

...
  {
   "id": "/Task/c21b6300-19d7-4a39-b6b0-b383a848fb64",
   "Name": "Concept phase",
   "Manager": {
    "id": "/User/9bcd7df3-4f6b-4272-944c-219b65b7862a",
    "Name": "Sam",
    "MobilePhone": "05497489057"
   },
   "Parent": {
    "id": "/Task/0f2e3ee7-575e-4617-a034-34f480872634",
    "Parent": null
   }
  }
...

Note that when requesting nested fields the result will contain the nested fields as a sub object. If multiple nested fields are requested, they will be part of the same nested object.
In the above example, requesting both Manager.Name and Manager.MobilePhone will result in a single Manager field in the response which contains both values.
You can include up to 4 levels of nested fields

Using IN condtions

IN Conditions can be used to compare a field to a list of values or to the result of a query.
For example, the following query will find all tasks in Active or Draft state

SELECT Name, State
FROM Task
WHERE State IN ('Active','Draft')

You can also use NOT IN shown in the following example which finds all tasks which are neither Active nor Draft

SELECT Name, State
FROM Task
WHERE State NOT IN ('Active','Draft')

The following example shows how to use sub queries in a condition to get all the tasks that are managed by my "Direct Reports" (Users which I am their direct manager)

SELECT Name
FROM Task
WHERE Manager in (
    SELECT ID 
    FROM user
    WHERE DirectManager = "/User/9bcd7df3-4f6b-4272-944c-219b65b7862a"
)

The following example shows how to find Issues which are resolved in a project with tasks that I manage

SELECT Title, PlannedFor
FROM Issue
WHERE PlannedFor IN (
    SELECT project 
    FROM task
    HERE manager = "/User/9bcd7df3-4f6b-4272-944c-219b65b7862a"
)

IN Subquery conditions can be used to "simulate" conditions on nested fields.
For example, suppose you want to find all the tasks that are in a project with a %completed value greater than 90. One way to present this query is like this:

SELECT Name
FROM Task
/* The following syntax is not correct */
WHERE Project.PercentCompleted > 90

Conditions on nested fields are not valid in CZQL so the above query will fail. However, it can be rewritten using an IN condition and achieve the same results:

SELECT Name
FROM Task
WHERE Project IN (
    SELECT ID 
    FROM project
    WHERE PercentCompleted > 90
)

Aggregation And Grouping

CZQL allows you to aggregate and group your query results using grouping functions such as Sum() or Max() and the GROUP BY clause.
If a CZQL query does not contain a GROUP BY clause, the aggregate function will run on all the entities that match the query. For example, the following query will return the count of tasks in a project and their average %complete:

SELECT COUNT() AS ProjCount, AVG(PercentCompleted)
FROM Task
WHERE Project = "/Project/2e4cad28-3718-4eac-8d78-ce968233f551"

Response:

{
 "entities": [
  {
   "ProjCount": 67,
   "Avg_PercentCompleted": 36.656716417910445
  }
 ],
 "paging": {
  "from": 1,
  "limit": 100,
  "hasMore": false
 }
}

To group the results by a certain field either add the field to the GROUP BY clause or to the SELECT fields. This means that the following three queries are essentially identical:

SELECT Importance, COUNT() AS Cnt
FROM Task

SELECT COUNT() AS Cnt
FROM Task
GROUP BY Importance

SELECT Importance, COUNT() AS Cnt
FROM Task
GROUP BY Importance

Response:

 "entities": [
  {
   "Importance": {
    "id": "/Importance/High"
   },
   "Cnt": 13
  },
  {
   "Importance": {
    "id": "/Importance/Normal"
   },
   "Cnt": 205
  }
 ]

You can use nested fields in aggregate queries, however, grouping will still be performed on the reference field. For example, the following query will group by the Manager field and not by the FirstName field:

SELECT Manager.FirstName, COUNT() AS Cnt
FROM Task

Response:

...
  {
   "Manager": {
    "id": "/User/86e23e39-7fdb-4450-b7e9-2799ed8130c5",
    "FirstName": "Dan"
   },
   "Cnt": 48
  },
  {
   "Manager": {
    "id": "/User/f77bba29-c174-4726-a195-233da60bf256",
    "FirstName": "Dan"
   },
   "Cnt": 9
  }
  ...

Aggregate results can be sorted by any field from the SELECT list.
The following query counts the number of tasks for each State and returns the list ordered from the highest to lowest number.

SELECT COUNT() AS Cnt
FROM Task
GROUP BY State
ORDER BY Cnt DESC

Response:

...
{
   "State": {
    "id": "/State/Active"
   },
   "Cnt": 83
  },
  {
   "State": {
    "id": "/State/Draft"
   },
   "Cnt": 74
  },
  {
   "State": {
    "id": "/State/Completed"
   },
   "Cnt": 49
  },
  {
   "State": {
    "id": "/State/On Hold"
   },
   "Cnt": 11
  },
  {
   "State": {
    "id": "/State/Cancelled"
   },
   "Cnt": 1
  }
  ...

Relations

CZQL allows you to include data from entities related to the main entity your are querying if a Relation exists between them. For example, you can return Tasks and Resources in a single query result by using Relations.
The following example retrieves all the tasks that are managed by a specific user along with their resources:

SELECT name, (SELECT name FROM Resources) 
FROM task 
WHERE Manager = '/User/69936402-07d6-4620-9f36-9480a6527f6f'

Response:

  "entities": [
    {
      "id": "/Task/00d4c913-b125-4e20-8096-9b5b7bb52091",
      "name": "Validate teams allocation according to contract",
      "Resources": {
        "entities": [
          {
            "id": "/User/69936402-07d6-4620-9f36-9480a6527f6f",
            "name": "Sam"
          },
          {
            "id": "/User/0dba9ecc-ad51-4d57-8f6c-c5d9b9ec85e7",
            "name": "Joy Stevens"
          }
        ],
        "paging": {
          "from": 2,
          "limit": 30,
          "hasMore": false
        }
      }
    },
    {
      "id": "/Task/92e632c5-0c88-46c7-9c71-483ab237f29f",
      "name": "Itterate with BrothersCorp on results",
      "Resources": null
    }
...

CZQL Bind Parameters

CZQL supports Bind Parameters to make it easy and safe to build dynamic queries at runtime without needing to escape user input.

For example, suppose you are building a public form that allows customers to search your Issues database.
Because your Issues database contains both publicly visible Issues and Issues which are internal, you create a field called "C_Public" and only allow searching on Issues that has this field checked.
Assuming your form contains a single field called Title, you might attempt to implement that search using the following pseudocode:

function findIssues(title)
{
   var query = 'SELECT Title FROM Issue Where C_Public=true AND Title LIKE "%' + 
                title + '%"';
   return ExecuteQuery(query);
}

This means that if a user typed the text Error in the search form, the result query will be:

SELECT Title FROM Issue Where C_Public=true AND Title LIKE "%Error%"

Now consider what will happen if a user tries to search for a phrase with quotes and enter the following text in your search form: "404 Error"
The query that will be generated will include the quotes and will look like this:

SELECT Title FROM Issue Where C_Public=true AND Title LIKE "%"404 Error"%"

Note how the quotes that the user entered has "closed" the first quotes and made the query invalid. Running this query will result in the following error: Invalid Query: Invalid input found: '404'
An even more serious issue is that a malicious user can access non public issues by submitting a search text that will completly change the query.
For example, entering the following search text: " OR C_Public=false OR title LIKE " will result in the following query being executed:

SELECT Title FROM Issue 
Where C_Public=true AND Title LIKE "%" OR C_Public=false OR title LIKE "%"

The above query will essentially return all the Issues in your database without filtering only Public issues.
The way to overcome these issue is to use Bind Parameters. Using bind parameters, you build your query once and mark the places in the query that will be replaced in runtime. When running the query, you pass the runtime values in a separate parameter.
For example, the above pseudocode will change to the following:

function findIssues(title)
{
   var query = 'SELECT Title FROM Issue Where C_Public=true AND Title LIKE :searchText';
   return ExecuteQuery(query, {searchText: '%'+title+'%'});
}

The actual REST request will look like this:

POST /V2.0/services/data/query

{
    "q": "SELECT Title FROM Issue Where C_Public=true AND Title LIKE :searchText",
    "parameters": {
        "searchText": "%\"404 Error\"%"
    }
}