ATG Repository Queries

ATG RQL

Oracle ATG Repository Query Language, or RQL, is a generic language for formulating queries that map to any repository implementation, such as SQL or LDAP.

Instead of operating on tables and columns, RQL works with objects and their properties defined in Repository Definition. RQL queries are translated by Repository into conventional SQL queries which in turns perform action on database.

RQL is a textual query syntax that is similar to SQL. It describes the set of conditions that must be matched by items of a particular item descriptor.

Basic ATG grammar you can find in documentation that is available online

But developers often face some non-trivial queries to be built. And at first sight, it looks like impossible to build.

Please find below a few tips how to improve your queries

Multi-Valued Property Queries

Logical operators and the MATCH and MATCHES operators (described in the later section Full Text Search Queries) should only be applied to scalar properties. And it doesn’t work for arrays and collections.

When you need to build a query to filter out records based on values in an array or collection property, you can use INCLUDES:

interests INCLUDES "biking“
interests INCLUDES ANY { "biking", "swimming" }
interests INCLUDES ALL { "biking", "swimming" }
addresses INCLUDES ITEM (zip = "48322" AND state = "MI")

RQL RANGE

Many queries have the potential for returning large result sets. To decrease load on database and to improve performance of your application, you most likely would decide to use RANGE to control number of returned records

```
address.zip = "94105" RANGE +10
```

This causes only the first 10 results to be returned.

```
address.zip = "94105" RANGE 10+
```

This causes the first 10 results to be skipped, and the remaining results to be returned.

```
address.zip = "94105" RANGE 40+10
```

This skips the first 40 results, then returns up to the next 10 results.

Repository Query API

In some cases, RQL is not flexible enough and developers need to use Repository Query API to build queries

atg.repository.QueryBuilder

Query Builder helps to define the available query operations and build Query objects

ComparisonQuery

RepositoryView userView = getRepository().getItemDescriptor(USER).getRepositoryView();
QueryBuilder userBuilder = userView.getQueryBuilder();
QueryExpression userType = userBuilder.createPropertyQueryExpression(USER_TYPE);
QueryExpression two = userBuilder.createConstantQueryExpression(USER_TYPE_2);
Query userTypeIsTwo = userBuilder.createComparisonQuery(userType, two, QueryBuilder.EQUALS);
RepositoryItem[] answer = userView.executeQuery(userTypeIsTwo);

PatternMatchQuery

RepositoryView employeeView = getRepository().getView(USER);
QueryBuilder queryBuilder = employeeView.getQueryBuilder();
QueryExpression propertyExpression = queryBuilder.createPropertyQueryExpression(“login”);
QueryExpression valueExpression = queryBuilder.createConstantQueryExpression(“name”);
Query accountQuery = queryBuilder.createPatternMatchQuery(propertyExpression, valueExpression, QueryBuilder.CONTAINS);
RepositoryItem[] repositoryItems = employeeView.executeQuery(accountQuery);

IncludesQuery

RepositoryView employeeView = getRepository().getView(USER);
QueryBuilder queryBuilder = employeeView.getQueryBuilder();
QueryExpression propertyExpression = queryBuilder.createPropertyQueryExpression("user_id");
QueryExpression valueExpression = queryBuilder.createConstantQueryExpression(ids);
Query employeeQuery = queryBuilder.createIncludesQuery(valueExpression, propertyExpression);
RepositoryItem[] repositoryItems = employeeView.executeQuery(employeeQuery);

Complex Query

You can create complex queries combining query expressions:

RepositoryView userView = getRepository().getItemDescriptor(USER).getRepositoryView();
QueryBuilder userBuilder = userView.getQueryBuilder();
QueryExpression userType = userBuilder.createPropertyQueryExpression("userType");
QueryExpression two = userBuilder.createConstantQueryExpression(2);
Query userTypeLTTwo = userBuilder.createComparisonQuery(userType, two, QueryBuilder.LESS_THAN);
QueryExpression login = userBuilder.createPropertyQueryExpression("login");
QueryExpression j = userBuilder.createConstantQueryExpression("j");
Query startsWithJ = userBuilder.createPatternMatchQuery(login, j, QueryBuilder.STARTS_WITH);
Query[] pieces = { userTypeLTTwo, startsWithJ };
Query andQuery = userBuilder.createAndQuery(pieces);
RepositoryItem[] answer = userView.executeQuery(andQuery);

atg.repository.QueryOptions

QueryOptions class can specify ways that a query can be modified:

let you limit the size of the result set,
direct how the result set should be sorted,
precache specified properties

RepositoryView view = getRepository().getView(USER);
Query query = view.getQueryBuilder().createUnconstrainedQuery();
String[] precachedPropertyNames = { "login", "password" };

SortDirectives sortDirectives = new SortDirectives();
sortDirectives.addDirective(new SortDirective( "login", SortDirective.DIR_ASCENDING));

RepositoryItem[] items = view.executeQuery(query, new QueryOptions(0, 5, sortDirectives, precachedPropertyNames));

More details about Repository Queries you can find in Oracle ATG Documentation