COMAND API Queries

Get Methods

Queries are performed on a repository or collection using their "get methods":

get($query, $options) - Get results as a collection of zero or more objects.
get_first($query, $options) - Get the first result as a single object, or NULL if no result.
get_rows($query, $options) - Get results as a row set of zero or more rows.
get_row($query, $options) - Get the first result as an associative array, or NULL if no result.

In addition, a query object can be created to programmatically construct a query in pieces, which is ultimately processed with "get methods" with the same names as those listed above.

The query parameter of the "get methods" above, and parameters passed to the query object methods, can be provided in a number of formats. The following sections describe the formats, and assume an understanding of the query languages described in the Query Guide.

Query Formats

API queries can be expressed the following ways.

Query String - A text-based query in one of the languages described in the Query Guide. It is the only format that can express cPath and cQuery queries. Query strings are straight-forward, but least efficient to process because they must be interpreted.
```
$repo->get("SELECT Name FROM Contact WHERE DOB<'2000-01-01'");
```
Query Object - A programmatically constructed cQL query. Each clause and clause element is assembled with individual method calls.
```
$repo->query()
    ->select("Name")
    ->from("Contact")
    ->where("DOB<'2000-01-01'")
    ->get();
```
cQL Array - An associative array with keys that specify cQL clauses (select, from, in, where, group by, order by and limit), and values of the associated query expressions.
```
$repo->get([
    "select" => ["Name"],
    "from" => ["Contact"],
    "where" => ["<", "DOB", "'2000-01-01'"]
]);
```

Query Expression Formats

Query Object method parameters and cQL Array values can define expressions in the following formats.

String Expression - A single expression or nested set of expressions, as they would appear in a query string.
```
$query->where("DOB<'2000-01-01'");
```
Parameter Expression - A pair of strings passed as parameters where the first parameter provides the first operand and optional space separated operator, and the next provides the second operand.
```
$query->where("DOB <", "'2000-01-01'");
```

Associative Array Expression

$query->where(["DOB <" => "'2000-01-01'"]);

Array Expression - Prefix notation arrays.

$query->where(["<", "DOB", "'2000-01-01'"]);

Query Processing

Queries are parsed from the various formats above into expressions and evaluated. Evaluation is handled by the appropriate repository or collection query processor.

Repository Query Processor - Queries performed against a repository are translated into an appropriate format based on the repository's storage engine. For example, if a repository uses an SQL storage engine, the query will be translated to SQL and executed to retrieve the appropriate objects and fields. For more information, see repository queries
Collection Query Processor - Queries performed against a collection are processed in PHP. For more information, see colleciton queries.

For information about query performance and object caching, see Repository Object Cache.

Query String

A query string corresponds directly to cPath, cQL or cQuery, as described in the Query Guide, because it is essentially in the same format, only encapsulated in a PHP string. The only differences to consider are related to PHP string syntax. For more information about that, see PHP Strings documentation.

Query Object

A query object is a programmatically constructed cQL query. Each clause and expression can be broken into individual method calls.

A query object is created by the repository query method.

$query = $repository->query();

Once a query object is created, query methods add cQL clauses and expressions to the query.

$query->select("Name");
$query->from("Contact");
$query->where("DOB<'2000-01-01'");

After all clauses and expressions are added, a "get method" executes the query and returns the result.

$collection = $query->get();

Query object methods update and return the query object, so they can be chained together.

$collection = $repository->query()
    ->select('FirstName', 'LastName')
    ->from('Contact+')
    ->where('LastName', 'Smith')
    ->group_start()
        ->where('FirstName !=', 'LastName')
        ->or_where('Age >', 19)
    ->group_end()
    ->order_by(['LastName'=>'desc'])
    ->get();

cQL Array

An associative array with keys that correspond to the cQL clauses: select, from, in, where, group by, order by and limit. The value of each key is detailed below, and may be expressed in any of the Query Expression Formats except Parameter Expression.

The API processes this format more efficiently than the other formats.

select - An array of array expressions, where each specifies a field or expression to retrieve.
```
"select" => ['AS', ['SUM','Transactions.Amount'], 'Sum']
```
from - A string or array of strings that specify the content type(s) to retrieve, with '+' symbol support.
```
"from" => ['Person', 'Contact+']
```
in - A cPath and keyword string, as described in the IN Clause section of the cQL Query Guide.
```
"in" => "/Contacts AND PARENTS"
```
where - An expression in one of the query expression formats.
```
"where" => ['=','Title', '"Hello"']
```
group by - An array of query expressions, where each specifies a field or expression to group by.
```
"group by" => ['LastName', ['YEAR','DOB']]
```
order by - An array where each element is a string, expression in one of the query expression formats or an array with two elements: query expression and keyword string 'ASC' or 'DESC'.
```
"order by" => ['LastName', 'FirstName', ['DOB','DESC']]
```
limit - An array with one or two elements that specify the limit and offset respectively.
```
"limit" => [10,20]
```

String Expression

A string expression corresponds directly to cQL Expression, as described in the Query Guide, because it is essentially in the same format, only encapsulated in a PHP string. The only differences to consider are related to PHP string syntax. For more information about that, see PHP Strings documentation.

Parameter Expression

API query methods can build an Expression Array one part at a time (by clause and even element within a clause). In addition to the expression array and query string forms described above, those methods also except one or more parameter expressions. Parameter expressions are a clean way to pass an expression as one or more pairs of method parameters.

A parameter expression is simply two consecutive parameters, which are treated as a key/value pair in a comparison. The example below adds an expression equivalent to Name = "Fred" to the WHERE clause.

$query->where('Name', '"Fred"');

The default comparison is equals. However, any comparison can be specified after a space in the first/field parameter.

$query->where('Name !=', '"Fred"');

Fields can use dot-notation to traverse objects, just like in a cQL string.

$query->where('Address.City', '"Buffalo"');

Finally, some query methods can be passed multiple consecutive pairs, which will be combined appropriately for the method. For example, the where method will combine them with a logical AND.

// equivalent to Address.City="Buffalo" AND Address.State="NY"
$query->where('Address.City', '"Buffalo"', 'Address.State', '"NY"');

Associative Array Expression

Associative arrays can be used instead of parameter expressions described in the section above. This is nice if you're familiar with other PHP query libraries that use a similar form.

An associative array expression is simply an associative array with one or more key/value pairs for comparison. The example below adds an expression equivalent to Name = "Fred" to the WHERE clause.

$query->where(['Name'=>'"Fred"']);

The default comparison is equals. However, any comparison can be specified after a space in the key/field parameter.

$query->where(['Name !='=>'"Fred"']);

Fields can use dot-notation to traverse objects, just like in a cQL string.

$query->where(['Address.City'=>'"Buffalo"']);

Finally, some query methods can be passed multiple consecutive pairs, which will be combined appropriately for the method. For example, the where method will combine them with a logical AND.

// equivalent to Address.City="LA" AND Address.State="CA"
$query->where(['Address.City'=>'"LA"', 'Address.State'=>'"CA"']);

Array Expression

An array expression uses prefix notation. The array starts with an operator, followed by one or more operands. Each operand may be an expression element or array expression. For example:

// simple comparison: (Amount >= 10)
$expression = ['>=', 'Amount', 10];

// nested comparisons: (Amount >= 10) AND (Amount <= 20)
$expression = array(
	'AND',
	['>=', 'Amount', 10],
	['<=', 'Amount', 20]
);

Expression Element

An expression element represents a Field, String, Number, Variable or Attribute for an operand of an array expression, value of an associative array expression or second parameter of a parameter expression.

Field - A field is specified with a dot-notation string.

$Field1 = "FieldName";
$Field2 = "ObjectFieldName.SubFieldName";
$Field3 = new \comand\repository\expression\field('FieldName');

String - A string value is specified as a quoted string within a PHP string.

$String1 = "'Some Value'";
$String2 = '"Some Value"';
$String3 = '"Don\'t forget to escape quotes."';
$String4 = new \comand\repository\expression\string('Explicit string.');

Number - A numeric value is specified like a standard PHP number.

$Number1 = 20;
$Number2 = 20 * 15;
$Number3 = 20.5;
$Number4 = new \comand\repository\expression\value(20.5);

Variable - A query variable (aka bind variable or placeholder) is specified as a string that contains a question mark or colon followed by a name. Variable values are defined in an array (for question mark notation) or associative array (for name notation) associated with the "bind" key of a "get method" option parameter.
```
$Variable1 = '?';
$Variable2 = ':Title';
$Variable3 = new \comand\repository\expression\variable('Title');
```

Attribute - An attribute is specified as a string where the first character is the '@' symbol.

$Attribute1 = "@";
$Attribute2 = "@Tag='Family'";
$Attribute3 = "@(Objects)Tag='Family'";
$Attribute4 = new \comand\repository\expression\attribute(
    "Tag", "'Family'", "Objects"
);