When search results are displayed to the user, it is often desirable to let the user choose the displayed columns. For this purpose, the ResultPreferencesEngine provides a simple API for retrieving and setting the preferred column layout based on the result type (e.g. Articles or Images). To select these user-defined column layouts, add the @* virtual property:

SELECT caption, @*

This is the only virtual property that actually expands to multiple columns, so it should appear at the end of the select clause.

To select all available columns, use the wildcard selector *. The wildcard expands to all properties of the content type returned to the user. When the result rows contain objects of more than one type, only the properties assigned to the root type are selected (i.e. properties that are defined for all types). This example selects all properties of the article type, since only articles (with a caption) will be returned:

SELECT *
WHERE #article/title IS NOT NULL

A main feature of the [fleXive] search engine is full-text indexing. By specifying the "*" property in the where clause, you will search across all indexed properties of a content.

SELECT @pk, caption
WHERE * = 'test'

When specifying a search property by name, you search across all properties of the given name. If you want to search in specific assignments, i.e. only in the address assignment of your contact data type, you have to either specify the assignment ID (prefixed by a # character) or the XPath prefixed by the type and without cardinalities instead of the property name.

Select the property caption and search in the assignment with ID=25 for main street:

SELECT @pk, caption
WHERE #25 like 'main street'

Select the assignment ARTICLE/TEASER/TEASER_TITLE where the assignment ARTICLE/TITLE starts with Earthquake:

SELECT @pk, #ARTICLE/TEASER/TEASER_TITLE
WHERE #ARTICLE/TITLE LIKE 'Earthquake%'

The virtual property @permissions selects the permissions of the calling user on the content instance:

SELECT @pk, @permissions

[fleXive] also supports searching inside the content tree and returns tree paths of contents linked in the tree.

FxSQL supports date and time functions similar to MySQL for FxDate and FxDateTime properties. They can be used to select or query against a partial date value. For example, this query selects all contents created in March 2008:

SELECT created_at
WHERE YEAR(created_at) = 2008
  AND MONTH(created_at) = 3

Date and time functions may be used both in the SELECT and WHERE clauses. When a function is used in a column select, it may also be used for sorting if you specify the column index instead of the name, for example:

SELECT created_at, SECOND(created_at)
ORDER BY 2

To explicitly limit the types returned by a query, you can use explicit type conditions. FxSQL also supports sub- and supertype queries.

Selecting properties with data type SelectOne returns the complete select item. You can also query individual columns as they are stored in the table FXS_SELECTLIST_ITEM, which is useful for custom sorts (otherwise the result can only be ordered by the system-internal item ID). Useful column names include ID, NAME, DATA, COLOR, POS (for example: SELECTPROP.POS to select the item position).

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.

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.

Filters applicable to the [fleXive] content table:

General filters:

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

For testing and documentation purposes you can embed comments in your query. For a standard line-based SQL comment use "--", for an inline or multiline comment use C-style comments in the form of "/*...*/"

SELECT /* inline comment */ id
WHERE id > 0    -- conditions

The following operators are supported by FxSQL. Note that not all data types support all operators.

Currently all conditions in FxSQL queries involve at least one literal value, e.g. WHERE title = 'test'. The following table lists all supported literal value formats and the [fleXive] types they may be applied to.

The basic query condition specifies

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

Query filters provide filtering capabilities on top of the search result. Currently only the type and briefcase filters are supported.

The SqlQueryBuilder provides support for building nested conditions like caption LIKE 'Test%' AND (created_by = 21 OR created_by = 22).

When submitting a query directly with the SqlQueryBuilder, miscellaneous search parameters can be set. Note that these parameters are not part of the FxSQL query, so if you submit the search by yourself, you cannot use these methods.

There are two ways of accessing the result rows:

Sometimes you do not want to work with the full result table, but only need a list of property values, for example primary keys. The FxResultSet#collectColumn(int) method projects a single column of the result set to a linear list. For example, to collect all primary keys of a [fleXive] type you submit a query against that type, selecting only the @pk column, and then project the first result column using collectColumn:

final List<FxPK> folderPks =
    new SqlQueryBuilder().select("@pk").type("FOLDER").getResult().collectColumn(1);