CoreMedia Unified API Developer Manual, Version 7.5.45-10
Chapter 5. The Content Repository | 5.5. Query Service

The QueryService allows synchronous, structured queries against the content repository. A query is a string formulated in the CoreMedia query language, with an optional array of parameter objects which may be referenced from the query string. The number of results to return can be limited. There are two variants of queries, those applying to content objects and their current property values, and those applying to versions (including working versions). Queries are initiated with the poseContentQuery and poseVersionQuery, respectively.

A query string expresses a condition on content objects. The QueryService returns all Content or Version objects in the repository for which the condition is true. The condition is made up of a logical combination (AND, OR, NOT) of type constraints, comparisons and tests, which may refer to the object's properties.

A query may also refer to getters defined in the Content, Version and PublicationService interfaces in the same way it refers to user-defined properties, by giving its name. The names of API methods are transformed as follows:

An implied property based on a Version getter is only defined for documents, not for folders. In a content query, it implicitly refers to the working version for a checked out document, or to the latest version for a checked in document.

There are three implied properties with a deviating semantics:

The following implied properties are currently defined:

When an ORDER BY clause is given, the query result is sorted according to the values of the given properties. These properties must be defined for all content types for which the condition may be true.

Please refer to the Javadoc of class QueryService for a comprehensive list of implied properties available in query expressions.

The query syntax is as follows:

query ::=
  conditional_expression [ order_by ] [ limit ]
  ;

order_by ::=
  ORDER BY order_entry {"," order_entry}
  ; 

limit ::=
  LIMIT numeric_literal
  ;

order_entry ::=
  property [ ASCENDING | ASC | DESCENDING | DESC ]
  ;

conditional_expression ::=
  TYPE ["="] type { "," type } [":" conditional_expression]
  | conditional_expression OR conditional_expression
  | conditional_expression AND conditional_expression
  | NOT conditional_expression
  | ( conditional_expression )
  | BELOW content
  | REFERENCES content
  | property REFERENCES content
  | REFERENCED
  | REFERENCED BY versionOrContent
  | property IS [NOT] NULL
  | comparison_expression
  | contains_expression
  | value_expression
  ;

type ::=
  identifier
  ;

comparison_expression ::=
  value_expression comparison_operator value_expression
  ;

comparison_operator ::=
  "=" | ">" | ">=" | "<" | "<="
  ;

contains_expression ::=
  property CONTAINS literal_expression
  | property CONTAINS EXACT literal_expression
  | property CONTAINS PREFIX literal_expression
  | property CONTAINS STEM literal_expression
  ;

value_expression ::=
  property
  | literal_expression
  ;

property ::=
  implied_property
  | identifier
  ;

content ::=
  literal_expression
  ;

version ::=
  literal_expression
  ;

versionOrContent ::=
  literal_expression
  ;

literal_expression ::=
  string_literal
  | numeric_literal
  | boolean_literal
  | DATE string_literal
  | PATH string_literal
  | USER string_literal
  | ID string_literal
  | input_parameter
  ;

boolean_literal ::=
  TRUE
  | FALSE
  ;

	Note
	The operator "=" in a comparison expression of String literals is handed over to the database. Thus, it depends on the database if the operator is case-sensitive or not.

Identifiers consist of Java identifier characters. Where the name of an identifier collides with a keyword or an implied property, the identifier can be enclosed in double quotes to preserve its meaning as an identifier. Examples:

String literals are delimited by single quotes. A single quote inside a string literal is represented by two successive single quotes. Examples:

Numeric literals conform to Java syntax. Essentially, a numeric literal is a sequence of digits, optionally preceded by a minus sign. Examples:

As date literals, the String has to be of the form recognized by DateConverter. This class generates and parses a subset of ISO8601 strings, namely those matching yyyy-MM-dd'T'HH:mm:ssTZD where the time zone distance TZD is expressed as +hh:mm or -hh:mm. Examples:

PATH literals denote a content by giving its path, beginning at the root folder. It is an error if no content exists at the given path. Examples:

USER literals denote a user name and a domain name separated by an @ character. If the domain name is empty, the @ character may be omitted. Examples:

ID literals denote a content, version or user by giving its ID, as returned by CapObject.getId(). Examples:

An input parameter refers to an object passed along with the query string. Input parameters are represented by a question mark immediately followed by a sequence of digits, which represents the zero-based index of the parameter object. Examples:

Type rules

Queries are strongly typed. For example, when you try to compare a String with an Integer, an error (MalformedQueryException) will be reported. There are rules that govern the required types for subexpressions, and the resulting type of each expression in the grammar above.

The following tables show all possible types that occur in subexpressions of a query. The second column shows corresponding Java types, which is relevant for parameter objects and implied properties. The third column shows the corresponding CapPropertyDescriptor type of a content property, which is relevant for non-implied properties.

The type of an implied_property clause depends on the return type of the corresponding getter method. For example, the return type of Content#getName() is java.lang.String, so the type of the expression name is String. There are three exceptions: For id, the type is Content. For version, the type is Version. For containsWideLink, the type is Boolean.

An identifier in a property clause is resolved as a property of the content type in the closest surrounding TYPE clause. The property type is then mapped to a query expression type using the table above. For example, assuming a string property called headline in a content type Article, the subexpression headline in the query "TYPE Article: headline CONTAINS 'foo'" would have type String. If there is no surrounding TYPE clause, the content type Content is assumed, which does not define any properties. If the TYPE clause lists multiple content types, the type of the property with the given name has to be same in all listed content types.

A property name in an order_entry clause is resolved as a property of the most specific type that can fulfill the query. Only properties of type Boolean, Date, Integer, or String are allowed.

In a REFERENCES clause, the property (if given) must be a Markup or Link List property.

In a comparison_expression, the types of both subexpressions must be the same, and must be one of Boolean, Date, Integer, or String, or one subexpression must be of type Integer and the other type must be integer compatible. User, Version and Content are integer compatible, by using the user id, content id, or version number for comparison.

The property in a CONTAINS expression must be a String or Markup property. The literal must be a String.

Where a value_expression is used as a conditional_expression, the value_expression must be a Boolean.

The expression type of an input_parameter depends on the class of the java object passed as a parameter. The mapping from Java type to expression type is given in the table above. For example, when passing in an instance of java.lang.String, the corresponding parameter expression will have the type String.

Where a content, version, or contentOrVersion clause is used in the grammar above, the literal_expression must have the respective type.

Interpretation

So far, you have seen when a query is syntactically correct, and when its types are correct. This section describes what the query expression actually means, where it was not explained before.

The following description is geared towards content queries (QueryService#poseContentQuery). In a version query, where the following description refers to a "content", the version's content is understood. Where the description refers to a "content object", the version itself is understood.

A "TYPE =" condition is true for a content if the content's type is one of the types listed, and the content fulfills the condition on the right hand (if given). This form does not take type inheritance into account.

A "TYPE" condition (without "=") is true if the content is a (direct or indirect) instance of at least one of the types listed, and the content fulfills the condition on the right hand (if given). This form takes type inheritance into account.

A "BELOW folder" condition is true for a content if the content is a child of the given folder. For the purpose of this condition, a folder is a child of itself.

A "REFERENCES target" condition is true for a content object if the content object contains a link to the given target in any markup or link list property.

A "property REFERENCES target" condition is true for a content object if the named property of the content object contains a link to the given target. The property must be a markup or link list property.

A "REFERENCED" condition is true for a content if it has at least one referrer, that is a content containing a link to this content in any markup or link list property.

A "REFERENCED BY contentOrVersion" condition is true for a content if the given content or version has a reference to this content in any markup or link list property.

A "property CONTAINS literal" condition is true for a content object for a string property, if the literal's string value is a substring of the content object's property's string value. For a markup property, all XML markup is discarded, and the string is searched for in the concatenated cdata elements.

CONTAINS EXACT, PREFIX, and STEM are only available if your database supports them. Currently only Oracle databases with the special module "multimedia" (formerly named interMedia) support this.

In an order_by clause, the first order_entry takes priority. If contents compare equal according to the first order entry, the next order entry is considered, etc. The ordering of String values is database dependent. The ordering of Date values ignores the time zone. The Boolean value FALSE is considered less than TRUE.

The limit clause limits the number of results that will be returned, and may improve performance, especially if only one result is required, and if sorting is not requested. It is equivalent to passing a limit argument to the query service method.

Examples

Search for a specific ID

This query returns the object with the given ID.

Collection<Content> result =
  qs.poseContentQuery("id = 1486")
  

Items reference another content item

This query gives all content items that reference a given item, defined by its content ID:

Collection<Content> result =
  qs.poseContentQuery("REFERENCES ID 'coremedia:///cap/content/1486'")
  

Articles that contain specific text

Consider that you are searching for all content items of type CMArticle that are not deleted and that contain the word 'Gin' in its detailText property:

Collection<Content> result =
  qs.poseContentQuery("TYPE CMArticle: NOT isDeleted AND detailText CONTAINS 'Gin'")
  

	Note
	This query will find all occurrences of 'Gin' even it is part of, for example, 'Ginger'. There is the clause CONTAINS EXACT which would only find 'Gin', but at the moment, it is only supported by Oracle databases with the multimedia module.

Search for the latest published versions

This query will find the last published versions of all CMChannel content items.

Collection<Content> result =
  qs.poseVersionQuery("TYPE CMChannel: versionIsPublished AND version=latestPublishedVersion")

Search for the latest published version of a specific content item

This query will find the last published versions of content item with ID 586.

Collection<Content> result =
  qs.poseVersionQuery("TYPE Document_: versionIsPublished AND version=latestPublishedVersion AND id=586")

Search for all content items lastly edited by a user

Consider that you are searching for all documents that were lastly edited by the user admin. Given that the variable qs holds a reference to the query service, you could issue the following statement:

Collection<Content> result =
  qs.poseContentQuery("TYPE Document_: editor = USER 'admin'")

All content items checked out by a user

Consider that you are searching for all documents that are checked out by the user admin.

Collection<Content> result =
  qs.poseContentQuery("TYPE Document_: isCheckedOut AND editor = USER 'admin'")

Published content below a specific folder

This statement retrieves arbitrary published content that is stored in the folder /Home. At most 50 results are returned.

Collection<Content> result =
  qs.poseContentQuery("isPublished AND BELOW PATH '/Home' LIMIT 50")

Items of type Articles, marked for deletion or withdrawal, approved before the given date

A parametrized query is shown that retrieves all documents of the type Article that are marked for deletion or withdrawal and were approved before the given date.

Collection<Content> result =
  qs.poseContentQuery("TYPE Article: placeApprovalDate < ?0 AND "+
  "(isToBeDeleted OR isToBeWithdrawn)",
  maxApprovalDate);