Search Engine

[fleXive] contents can be queried using a SQL-like dialect called FxSQL. The core idea of FxSQL is to provide a flat virtual table that contains all content instances visible to the current user, with full support for [fleXive] data types and multilingualism.

While it is possible to submit queries in plain FxSQL, there are alternatives for Java and Groovy developers in the form of query builders. They handle proper formatting of values and are the preferred way of formulating queries unless you need some of the more esoteric features of FxSQL.

The overall query layout is similar to SQL. Currently queries are always on the virtual table "content" that provides a flat view on all content instances.

SELECT @pk, caption           -- 1
FILTER version=live              -- 2
WHERE caption LIKE 'test%'       -- 3
ORDER BY caption                 -- 4

1

We select the virtual property @pk (the content primary key) and the standard property caption.

2

Filters work similar to the SQL HAVING clause by providing a post-processing filter mechanism on the results of the actual query. That is, filters are applied every time the search query is executed, and are not stored in the cached result tables. This means that changing filters usually does not invalidate the query cache, whereas changing the WHERE clause always does. In this example, we might want to select only live versions. If you do not specify a version filter, the search uses the maximum version of contents for matching conditions and selecting properties.

3

The WHERE clause supports most standard SQL operators for string and numerical comparisons. Here we select all contents whose caption starts with "test". Note that string comparisons are case insensitive.

4

To control the sorting of the result set, you can specify one or more columns in the ORDER BY clause. By default an ascending sort is used, to sort by descending values add DESC to the column alias (e.g. ORDER BY id DESC). Note that you can only use columns that are specified in the SELECT clause.

The rest of this section contains an enumeration of FxSQL features along with example queries demonstrating that feature.

Of course you can select all system properties, like acl or created_by, in your query. Some system properties are linked to their backing table. For example, the acl property is linked to the FXS_ACL table, so you can select any column in that table using a field suffix: acl.description selects the description column, and acl.color returns the color code. The most useful field, however, is the virtual acl.label field that returns the ACL label in the calling user's language.

Another useful example is to select the user name that created a content, instead of the account ID:

SELECT @pk, created_by.username

The following table contains all system properties that are linked to their backing table, including the most important fields. For a complete field list, please refer to the database schema.

Table 6.17. System property fields provided in FxSQL
Property (Table) Fields
acl (FXS_ACL) Selects the content ACL. Additional fields include:
acl.label

The localized label in the calling user's language.

acl.name

The unique name of the ACL.

acl.description

The ACL description.

acl.cat_type

The ACL category.

acl.color

The RGB color code used for this ACL.

created_by, modified_by (FXS_ACCOUNTS) Selects the user that has created/lastly modified the content ( created_by and modified_by both have the same fields). Interesting fields include:
created_by.username

The associated user's username.

created_by.email

The associated user's email address.

created_by.login_name

The unique login name of the user.

mandator (FXS_MANDATOR) Selects the mandator of the content instance. To select the mandator name, use mandator.name.
step (FXS_WF_STEPS) Selects the workflow step of the content instance. You can select the following fields:
step.label

The localized step label in the calling user's language.

step.workflow

The workflow ID of the step (and thus the complete workflow used for this content instance).

step.stepdef

The step definition ID.

step.acl

The ACL of the step.

To control the sort order of the result set, you can specify one or more columns in the ORDER BY clause. Usually you specify the name of a column previously selected in the SELECT clause, but you can also use the 1-based column index. The direction of the sort (ascending or descending) is set using the ASC and DESC modifiers, respectively. The following two queries order the result by the properties priority and caption (i.e. first the result is sorted by priority, then by caption), the first uses named columns, the second specifies the column indices instead:

SELECT @pk, caption, priority
ORDER BY priority DESC, caption

SELECT @pk, caption, priority
ORDER BY 3 DESC, 2

As described in the section called “Select user-defined columns”, @* expands to whatever columns the user defined for the result content type. This causes a problem when the result should be sorted manually by one of these columns, e.g. because the result table has sortable column headers (as in the result table in the backend administration application): the FxSQL parser has no way to expand @* (because the search has not been submitted yet and thus the content type is unknown), but yet it must be possible to sort by one of these columns. For example, this query does not work:

SELECT @*
ORDER BY caption DESC     -- caption not found in SELECT clause

As a workaround, it is possible to specify otherwise invalid column indices in the ORDER BY clause if @* was selected. Of course this may lead to runtime errors if the user-defined columns are less than the ORDER BY index. In the most probable use case, on-the-fly sorting of a result table, this is not a problem, since the user can only sort by columns that have already been rendered.

For example, the following is a valid FxSQL query but relies on the user having defined at least 3 columns for the result type:

SELECT @*
ORDER BY 3 DESC     -- order by the third column of @*

To ease the pain of having to learn yet another query language, the SqlQueryBuilder provides a thin builder interface for FxSQL queries. The query is created using chained calls to the builder and results in a FxSQL query. For example:

new SqlQueryBuilder()                                   // 1 
     .select("@pk", "created_at", "caption")            // 2
     .type("article")                                   // 3
     .orderBy("created_at", SortDirection.DESCENDING)   // 4
     .getResult();                                      // 5

1

A new query builder is created.

2

We select three columns, the virtual property @pk, the content creation date, and the article caption.

3

We want to search only for contents of type Article.

4

Order the result by creation date, return newest articles first (SortDirection.DESCENDING). You can issue multiple calls to orderBy for sorting by multiple columns. You can also specify the column index instead of the column name. If you specify the property name, you must ensure that this property is selected, otherwise the query builder throws a FxRuntimeException.

5

The getResult() call submits the search query to the search engine EJB and returns a FxResultSet.

A call to SqlQueryBuilder#getQuery() returns the actual FxSQL query, for example, the code snippet above built this FxSQL query:

SELECT @pk, created_at, caption
WHERE (typedef = 3)
ORDER BY created_at DESC

The basic query condition specifies

  1. a property or assignment whose value will be compared,

  2. a PropertyValueComparator specifying the compare operator (like equals or greater), and

  3. a constant value to compare the content value against, like "5" or "Test". Comparisons between content values are not supported.

SqlQueryBuilder offers an overloaded, general-purpose condition() method for specifying arbitrary conditions, and custom condition methods for tree queries and content type constraints:

condition(...)

Add a condition to the query. This method is overloaded to support both assignment and property queries, for the actual method signatures please refer to the [fleXive] JavaDoc. If you want to apply a function (e.g. YEAR(prop)), you have to use the generic condition(String, ...) methods.

isChild(nodeId)

Adds a tree search condition to the query that limits the search to children (direct and indirect) of the given tree node ID. See the section called “Tree Search”.

isDirectChild(nodeId)

Like isChild, but includes only direct children of the given node.

type(contentType)

Adds a content type constraint to the query, i.e. the expression will match only contents of the given type. Note that this is semantically different from a content type filter, which will be covered in the next section.

For Groovy developers, the GroovyQueryBuilder offers an even more convenient way of formulating FxSQL queries. Note that this class (like the rest of Groovy support classes) is still experimental, so not all features may work as intended.

Currently the Groovy builder supports defining the column selection, nested property conditions and briefcase filtering. The query builder actually builds a query tree similar to the JSF search query editor, so the result object is of type QueryRootNode. From this class you can get either the final FxSQL query from the sqlQuery property, or the SqlQueryBuilder which has been used for building the query from the queryBuilder property.


When a FxSQL query is submitted to the database, all rows are fetched at once and returned in a FxResultSet object. It contains all result rows within the user-defined limits (unlike the cursor-approach of JDBC), including miscellaneous information like the selected column names, or row count information.