Grammar

Luminate Online Query Language supports a basic subset of the SQL SELECT grammar:

query
  : 'select' fieldlist 'from' object ('where' criteria)?
    -> ^(ROOT ^('select' fieldlist) ^('from' object) ^('where' criteria)?)
  ;

fieldlist
  : select_field (',' select_field)*
    -> select_field+
  ;

select_field
  : FIELD | WILDCARD
  ;

object
  : FIELD
  ;

criteria
  : criterion (BOOLEAN^ criterion)*
  ;

criterion
  : criteriagrouping
  | basicoperator
  | isoperator
  ;

criteriagrouping
  : '(' criteria ')'
    -> ^(ROOT criteria)
  ;

basicoperator
  : FIELD OPERATOR operand
    -> ^(OPERATOR FIELD operand)
  ;


operand
  : FIELD
  | STRING_LITERAL
  | NUMBER
  | DATETIME
  | DATE
  ;

isoperator
  : FIELD IS isoperand
    -> ^(IS FIELD isoperand)
  ;

isoperand
  : (NOT)? NULL
  ;

BOOLEAN: ('and' | 'or');

OPERATOR: ('<>' | '>=' | '>' | '<=' | '<' | '=');

IS: 'is';

NOT: 'not';

NULL: 'null';

fragment
DIGIT: '0'..'9';

fragment
TIMEZONE: ('Z' | 'z') | (('+' | '-') DIGIT DIGIT ':'? DIGIT DIGIT);

DATE: DIGIT DIGIT DIGIT DIGIT '-' DIGIT DIGIT '-' DIGIT DIGIT TIMEZONE?;

DATETIME: DIGIT DIGIT DIGIT DIGIT '-' DIGIT DIGIT '-' DIGIT DIGIT ('T' | 't') DIGIT DIGIT ':' DIGIT DIGIT ':' DIGIT DIGIT TIMEZONE?;

NUMBER: DIGIT+ | DIGIT+ ('.' DIGIT*) | '.' DIGIT+;

FIELD: ('a'..'z' | 'A'..'Z') ('a'..'z' | 'A'..'Z' | '0'..'9' | '_' | '.')+;

WILDCARD: '*' | FIELD '*';

fragment
QUOTED_CHARACTER:
  ( ~( '\'' | '\\' ) ) | '\\' ( ( '\'' | '\\' ) );

STRING_LITERAL:
  '\''! ( QUOTED_CHARACTER )* '\''!;

WS: (' ' | '\t' | '\r' | '\n')+ { skip();};

The field and record type names in CQL do not have to map directly to schema column and table names. They may be aliases or be derived. For example, the SQL statement for a constituent query may involve a join to retrieve status information (such as whether a client is a donor).

The possible set of record-object types includes all sub-types of Record as defined by the Luminate Online WSDL. You can use the DescribeRecordType operation to determine whether a particular record type supports the Query operation, as well as which fields may be used as criteria in the where clause of a CQL query.

Note the order of fields in the SELECT clause is significant. A field out of order results in an InvalidQuery fault with the faultstring: 'Field order for (field) does not match the schema definition for record type (Record).'

The query parser will validate requested queries and convert them to an appropriate SQL SELECT statement for executing against the Luminate Online database.

Date Criteria

Date criteria must be specified in ISO 8601 format with an appropriate time zone offset. For example:

  select ConsName, UserName from Constituent where ModifyDate >= 2009-05-04T16:45:29+00:00

Note that the date should not be quoted.

Query Examples

select ConsName.FirstName, ConsName.LastName, UserName from Constituent where ConsName.FirstName = 'John'

select ConsName from Constituent where GroupId = 456

select Id,Name from Group where TypeId = 789

select * from Constituent where DatasyncDate is null

Note that the query language supports grouping of related fields in the select clause. For example, clients can request first and last name specifically, or they just request the Name object and get back a nested element containing title, first, middle, last, etc.