Previous
4.1.4 Query Language
4.1.4.1 Introduction
The Daisy Query Language can be used to search for documents (more precisely, document variants). In the Daisy Wiki, queries can be used in various places:
- explicitely via the "Query Search" page
- embedded inside documents
- embedded inside navigation trees
The implementation of various Daisy Wiki features is also based on queries, such as the recent changes page or the referrers page. And of course it is possible to execute queries from your own applications, using the HTTP interface or Java API.
The query language is a somewhat SQL-like language that allows to search on various document properties (including the fields), fulltext on the part content, or a combination of those. The sort order of the results can also be defined. The resulting document list is filtered to only include documents to which the user has at least read-live access.
An example query, searching all documents in a collection call "mycollection":
select id, name where InCollection('mycollection') order by name
Internally, non-fulltext queries are translated to SQL and executed on the relational database while fulltext queries are executed by Jakarta Lucene.
Allthough the query language is somewhat SQL-like, it hides the complexity of the actual SQL-queries that are performed by the repository server on the relational database, which can quickly grow quite complex.
Note: everytime in this document when we talk about "searching documents", this is equivalent to "searching document variants". The result of query is a set of document variants, i.e. each member of the result set is identified by a tripple (document ID, branch, language).
4.1.4.2 Query Language
4.1.4.2.1 General structure of a query
select ... where ... order by ... limit x option ...
The select and where parts are required, the rest is optional. Whitespace is of no importance.
4.1.4.2.2 The select part
The select part should list one or more identifiers, separated by commas. Available identifiers are listed further on.
4.1.4.2.3 The where part
The where part should contain a conditional expression, thus an expression which tests the value of identifiers using operators, or uses some built-in functions.
Besides the identifiers listed in the table below, the opertions AND and OR are supported, and parentheses can be used for grouping.
4.1.4.2.3.1 Operators & datatypes
|
string |
long |
double |
decimal |
date |
datetime |
boolean |
|
|---|---|---|---|---|---|---|---|
|
= |
X |
X |
X |
X |
X |
X |
X |
|
!= |
X |
X |
X |
X |
X |
X |
X |
|
< |
X |
X |
X |
X |
X |
X |
|
|
> |
X |
X |
X |
X |
X |
X |
|
|
<= |
X |
X |
X |
X |
X |
X |
|
|
>= |
X |
X |
X |
X |
X |
X |
|
|
[NOT] LIKE |
X |
||||||
|
[NOT] BETWEEN |
X |
X |
X |
X |
X |
X |
|
|
[NOT] IN |
X |
X |
X |
X |
X |
X |
|
|
IS [NOT] NULL |
X |
X |
X |
X |
X |
X |
X |
Wildcards for LIKE are _ and %, escape using \_ and \%.
All keywords such as AND, LIKE, BETWEEN, ... can be written in either uppercase or lowercase (but not mixed case).
If these operators are used on multi-value fields, they return true if at least one of the values of the multi-value field satisfies. See further on for a set of conditions specifically for multi-value fields.
4.1.4.2.4 Identifiers
The table below lists the available identifiers.
Some notes:
- identifier names are case sensitive
- non-searcheable identifiers are identifiers which can only be used in the select clause of the query, not in the where clause
- the datatype symbolic means it should be a string, but the string is internally translated into another code. For example, when searching on ownerLogin, the given string is internally translated to a user id, which is then used when performing the database search. This means that certain operators will not work on it or will be of little meaning (such as like, less then, greater then, ...)
- version dependent means that the searched or retrieved data is version dependent data. By default this will search in, or retrieve data from, the live version of the document, but by specifying the query option search_last_version (see further on) the last version can also be searched.
- the names in italic, i.e. partTypeName, fieldTypeName and customFieldName must be replaced by an actual name.
|
name |
searchable |
datatype |
version dependent |
remarks |
|---|---|---|---|---|
|
id |
yes |
long |
no |
|
|
name |
yes |
string |
yes |
|
|
branch |
yes |
symbolic |
no |
|
|
branchId |
yes |
long |
no |
|
|
language |
yes |
symbolic |
no |
|
|
languageId |
yes |
long |
no |
|
|
documentType |
yes |
symbolic |
no |
|
|
versionId |
yes |
long |
yes |
ID of the live version, or if the query option search_last_version is specified, of the last version |
|
creationTime |
yes |
datetime |
no |
|
|
ownerId |
yes |
long |
no |
|
|
ownerLogin |
yes |
symbolic |
no |
|
|
ownerName |
no |
string |
no |
|
|
summary |
no |
string |
no |
always of last published version |
|
retired |
yes |
boolean |
no |
|
|
private |
yes |
boolean |
no |
|
|
lastModified |
yes |
datetime |
no |
|
|
lastModifierId |
yes |
long |
no |
|
|
lastModifierLogin |
yes |
symbolic |
no |
|
|
lastModifierName |
no |
string |
no |
|
|
variantLastModified |
yes |
datetime |
no |
|
|
variantLastModifierId |
yes |
long |
no |
|
|
variantLastModifierLogin |
yes |
symbolic |
no |
|
|
variantLastModifierName |
yes |
string |
no |
|
|
%partTypeName.mimeType |
yes |
string |
yes |
|
|
%partTypeName.size |
yes |
long |
yes |
|
|
%partTypeName.content |
no |
xml |
yes |
only works for part types for which the flag 'daisy html' is set to true, and additionally the actual part must have the mime type 'text/xml' |
|
versionCreationTime |
yes |
datetime |
yes |
|
|
versionCreatorId |
yes |
long |
yes |
|
|
versionCreatorLogin |
yes |
symbolic |
yes |
|
|
versionCreatorName |
yes |
string |
yes |
|
|
versionState |
yes |
symbolic |
yes |
'draft' or 'publish' |
|
totalSizeOfParts |
yes |
long |
yes |
sum of the size of all parts in document |
|
versionStateLastModified |
yes |
datetime |
yes |
|
|
lockType |
yes |
symbolic |
no |
'pessimistic' or 'warn' |
|
lockTimeAcquired |
yes |
datetime |
no |
|
|
lockDuration |
yes |
long |
no |
(in milliseconds) |
|
lockOwnerId |
yes |
long |
no |
|
|
lockOwnerLogin |
yes |
symbolic |
no |
|
|
lockOwnerName |
no |
string |
no |
|
|
collections |
yes |
symbolic |
no |
The collections (the names of the collections) the document belongs too. Behaves the same as a multi-value field with respect to applicable search conditions. |
|
collections.valueCount |
yes |
symbolic |
no |
The number of collections a document belongs too. |
|
$fieldTypeName |
yes |
yes |
datatype depends on field type |
|
|
$fieldTypeName.valueCount |
yes |
long |
yes |
Useful for multi-value fields. Searching for a value count of 0 does not work, use the "is null" condition instead. |
|
#customFieldName |
yes |
string |
no |
4.1.4.2.5 Literals
4.1.4.2.5.1 String literals
Strings (text) should be put between single quotes, the single quote is escaped by doubling it, for example:
'''t is mooi weer vandaag'
4.1.4.2.5.2 Numeric literals
These consists of digits (0-9), the deicmal separator is a dot (.).
Numeric literals can be put between single quotes like strings, but it is not required to do so.
4.1.4.2.5.3 Date & datetime literals
Date format: 'YYYY-MM-DD'
Datetime format: 'YYYY-MM-DD HH:MM:SS'
4.1.4.2.6 Special conditions for multi-value fields
$fieldName has all (value1, value2, value3, ...)
Tests that the multi-value field has all the specified values (and possibly more).
$fieldName has exactly (value1, value2, value3, ...)
Tests that the multi-value field has all the specified values, and none more. The order is not important.
$fieldName has some (value1, value2, value3, ...) or $fieldName has any (value1, value2, value3, ...)
has some and has any are synomyms. They test that the multi-value field has at least one of the specified values.
$fieldName has none (value1, value2, value3, ...)
Tests that the multi-value field has none of the specified values.
In addition to these conditions, you can use is null and is not null to check if a document has a certain (multi-value) field. The special sub-identifier $fieldName.valueCount can be used to check the number of values a multi-value field has.
4.1.4.2.7 Other special conditions
4.1.4.2.7.1 InCollection
InCollection('collectionname' [, collectioname, collectionname])
Searches documents contained in at least one of the specified collections. To search documents that occur in multiple collections (thus in the intersection of those collections), use the function InCollection multiple times with AND in between: InCollection('collection1') and InCollection('collection2'). This also works for OR but in that case it is more efficient to give the collections as arguments to one InCollection call.
Instead of the InCollection condition, you can use the collections identifier in combination with the multi-value field search conditions such as has some, has all or has none for more powerful search possibilities. The InCollection condition predates the existence of multi-value fields, but remains supported.
4.1.4.2.7.2 LinksTo, LinksFrom, LinksToVariant, LinksFromVariant
LinksTo(documentId, inLastVersion, inLiveVersion [, linktypes]) LinksFrom(documentId, inLastVersion, inLiveVersion [, linktypes]) LinksToVariant(documentId, branch, language, inLastVersion, inLiveVersion [, linktypes]) LinksFromVariant(documentId, branch, language, inLastVersion, inLiveVersion [, linktypes])
Searches documents which link to or from the specified document (or document variant). The other two parameters, inLastVersion and inLiveVersion, are interpreted as booleans: 0 is false, any other (numeric) value is true.
If inLastVersion is true, only documents whose last version link to the specified document are included.
If inLiveVersion is true, only documents whose live version link to the specified document are included.
If both parameters are true or both are false, all documents are returned for which either the last or live version link to the specified document.
The optional parameter linktypes is a string containing a comma seperated list of the types of links to include, which is one or more of: inline, out_of_line, image, include or other.
4.1.4.2.7.3 IsLinked, IsNotLinked
IsLinked() IsNotLinked()
IsLinked() evaluates to true for any document which is linked by other documents, IsNotLinked() evaluates to true for any document that is not linked from any other document (thus not reachable by following links in documents, the navigation tree, or linked by the content of other parts on which link extraction is performed).
4.1.4.2.7.4 HasPart
HasPart('partTypeName')
Searches documents which have a part of the specified part type. This search is version-depedent.
4.1.4.2.7.5 HasPartWithMimeType
HasPartWithMimeType('some mimetype')
Searches documents having a part with the given mime type. This search is version-dependent. This uses a 'like' condition, thus the % wildcard can be used in the parameter. For example, to search all images: HasPartWithMimeType('image/%')
4.1.4.2.7.6 DoesNotHaveVariant
DoestNotHaveVariant(branch, language)
Searches documents that do not have the specified variant. See also the page on variants for more information.
4.1.4.2.8 Full text queries
For full text queries, the where part takes a special form. There are two possibilities: either only a full text search is performed, or the fulltext query is further restricted using 'normal' conditions. The two possible forms are:
... where FullText('word')
or
... where FullText('word') AND <other conditions>
for example:
... where FullText('word') AND $myfield = 'abc' AND InCollection('mycollection')
Note that the combining operator between the FullText condition and other conditions is always AND, thus the result of the full text query is further refined. The further conditions can of course be of any complexity, and can thus again contain OR.
If no order by clause is included when doing a full text query, the results are ordered according to the score assigned by the fulltext search engine.
The parameter of the FullText(...) function is a query which is passed on to the full text engine, in our case Lucene. See here.
The FullText() function can have 3 additional parameters which indicate if the search should be performed on the document name, document content or field content. By default, all three are searched. These parameters should be numeric: 0 indicates false, and any other value true.
For example:
FullText('word', 1, 0, 0)
Searches for 'word', but only in the document name.
Additionally, you can specify a branch and language as parameters to the FullText function, to specify that only documents of that branch/language should be searched. Thus the full syntax of the FullText function is:
FullText(lucene query, searchInName, searchInContent, searchInFields, branch, language)
Specifying the branch and language as part of the FullText function is more more efficient then using:
FullText(lucene query) and branch = 'my_branch' and language = 'my_language'
4.1.4.2.9 The order by part
The order by part is optional.
The order by part contains a comma separated listing of identifiers, each of these optionally followed by ASC or DESC to indicate ascending (the default) or descending order. The identifiers listed here have no connection with those in the select-part, i.e. it does not have to be subset of those.
"null" values are put at the end (when using ASC order).
4.1.4.2.10 The limit part
This can be used to limit the number of results returned from a query. This part is optional.
4.1.4.2.11 The option part
The option part allows to specify options that influence the execution of the query. The options are defined as:
option_name = 'option_value' (, option_name = 'option_value')*
Supported options:
|
name |
value |
default |
|---|---|---|
|
include_retired |
true/false |
false |
|
search_last_version |
true/false |
false |
|
style_hint |
(anything) |
(empty) |
include_retired is used to indicate that retired documents should be included in the result (by default they are not).
search_last_version is used to indicate that the last version of metadata should be searched and retrieved, instead of the live version. When using this, documents that do not have a live version will also be included in the query result (otherwise they are not included). Full text searches are always performed on the live data, regardless of whether this option is specified.
style_hint is used to supply a hint to the publishing layer for how the result of the query should be styled. The repository server does not do anything more then add the value of this option as an attribute on the generated XML query results (<searchResult styleHint="my hint" ...). It is then up to the publishing layer to pick this up and do something useful with it. For how this is handled in the DaisyWiki, see the page on Query Styling.
4.1.4.3 Example queries
4.1.4.3.1 List of all documents
select id, name where true
4.1.4.3.2 Search on document name
select id, name where name like 'p%' order by creationTime desc limit 10
4.1.4.3.3 Show the 10 largest documents
select id, name, totalSizeOfParts where true order by totalSizeOfParts desc limit 10
4.1.4.3.4 Show documents of which the last version has not yet been published
select id, name, versionState, versionCreationTime where versionState = 'draft' option search_last_version = 'true'
4.1.4.3.5 Overview of all locks
select id, name, lockType, lockOwnerName, lockTimeAcquired, lockDuration where lockType is not null
4.1.4.3.6 All documents having a part containing an image
select id, name where HasPartWithMimeType('image/%')