Query API

Last modified by Vincent Massol on 2024/02/26 17:31

magnifierAllows querying the XWiki Model using a variety of query languages
TypeJAR
Category
Developed byUnknown
Rating
1 Votes
LicenseGNU Lesser General Public License 2.1
Bundled With

XWiki Standard

Description

All data making up the XWiki application can be queried using one of the supported Query languages. Right now we support 3 languages:

Historically XWiki was only supporting HQL but that lead to complex queries in some instances and thus we've introduced XWQL which is a JPQL superset and which makes it much easier to write queries for the XWiki model, as you can see in the examples below.

The Query Module is only designed for READ-only queries. If you need to write something in the database, you should use the XWiki API.

Query Language Examples

Before 7.2 all queries starting with select require programming rights to execute.

Query DescriptionXWQLHQL
Query listing all documents<empty query><empty query>
Query listing all documents created between two dates

where doc.creationDate > :date1 AND doc.creationDate < :date2

Full example:

$services.query.xwql('where doc.creationDate > :date1 AND doc.creationDate < :date2').bindValue('date1', $datetool.toDate('yyyy-MM-dd', '2008-01-01')).bindValue('date2', $datetool.toDate('yyyy-MM-dd', '2008-02-01')).execute()

where doc.creationDate > :date1 AND doc.creationDate < :date2

Full example:

$services.query.hql('where doc.creationDate > :date1 AND doc.creationDate < :date2').bindValue('date1', $datetool.toDate('yyyy-MM-dd', '2008-01-01')).bindValue('date2', $datetool.toDate('yyyy-MM-dd', '2008-02-01')).execute()

Query listing all documents last updated by a given userwhere doc.author = 'XWiki.LudovicDubost'where doc.author = 'XWiki.LudovicDubost'
Query listing all documents containing XWiki Objects (XObject) of a given XWiki Class (XClass)from doc.object(XWiki.XWikiUsers) as user, BaseObject as obj where doc.fullName = obj.name and obj.className = 'XWiki.XWikiUsers'
Query on XObjects and filtering on XObject property valuewhere doc.author = 'XWiki.LudovicDubost' and doc.object(XWiki.XWikiUsers).email like '%xwiki.com', BaseObject as obj, StringProperty as prop where doc.fullName = obj.name and obj.className = 'XWiki.XWikiUsers' and obj.id=prop.id.id and prop.id.name='email' and prop.value like '%xwiki.com' and doc.author ='XWiki.LudovicDubost'
Query on 2 XObjectswhere doc.object(XWiki.XWikiUsers).email like '%xwiki.com'  and  doc.object(XWiki.ArticleClass).content like '%ludovic%', BaseObject as obj, StringProperty as prop, BaseObject as obj2, LargeStringProperty as contentprop where doc.fullName = obj.name and obj.className = 'XWiki.XWikiUsers' and obj.id=prop.id.id and prop.id.name='email' and prop.value like '%xwiki.com' and doc.fullName=obj2.name and obj2.className='XWiki.ArticleClass' and obj2.id=contentprop.id.id and contentprop.id.name='content' and contentprop.value like '%ludovic%'
Query listing all objects properties (MyClassPropertyName_Label) values sorted by id (MyClassPropertyName_Id) property of a given XWiki Class (MyLocation.MyClass). See other hql examplesselect prop.value from BaseObject obj, StringProperty as prop, StringProperty as propMyId where obj.className='MyLocation.MyClass' and prop.id.id=obj.id and prop.name='MyClassPropertyName_Label' and obj.id=propMyId.id.id and propMyId.id.name='MyClassPropertyName_Id' order by propMyId.value asc
Search blogs per category (MyCategory)where doc.fullName <> 'XWiki.ArticleClassTemplate' and :category member of doc.object(Blog.BlogPostClass).category  #set ($category = "blog.MyCategory")
, BaseObject as obj, DBStringListProperty as prop join prop.list list where obj.name=doc.fullName and obj.className='Blog.BlogPostClass' and obj.name<>'Blog.BlogPostTemplate' and obj.id=prop.id.id and prop.id.name='category'  and list='${category}' order by doc.creationDate desc
List all tagsselect distinct obj.tags from Document doc, doc.object(XWiki.TagClass) as objselect distinct tag from BaseObject as obj, DBStringListProperty as prop join prop.list as tag where obj.className='XWiki.TagClass' and obj.id=prop.id.id and prop.id.name='tags'
List all blog posts, published and not hidden (filter by multiple properties of an object)from doc.object(Blog.BlogPostClass) as blog where blog.published = '1' and :category member of blog.category and blog.hidden = '0' and doc.web = 'Blog' order by blog.publishDate desc, BaseObject as obj, IntegerProperty publishedProp, IntegerProperty hiddenProp, DateProperty publishDateProp, DBStringListProperty as categoryProp join categoryProp.list list where obj.name=doc.fullName and obj.className='Blog.BlogPostClass' and doc.space = 'Blog' and obj.id=categoryProp.id.id and categoryProp.id.name='category' and list='$category' and publishedProp.id.id = obj.id and publishedProp.id.name = 'published' and publishedProp.value = '1' and hiddenProp.id.id = obj.id and hiddenProp.id.name='hidden' and hiddenProp.value = '0' and publishDateProp.id.id = obj.id and publishDateProp.id.name='publishDate' order by publishDateProp.value desc
List documents having at least tags tag1 and tag2from doc.object(XWiki.TagClass) as tag where  'tag1' member of tag.tags and  'tag2' member of tag.tags, BaseObject as obj, DBStringListProperty as prop join prop.list item where obj.className='XWiki.TagClass' and obj.name=doc.fullName and obj.id=prop.id.id and prop.id.name='tags' and (lower(item)=lower('tag1') and (lower(item)=lower('tag2'))
Make a join between two objects that are not on the same document and are bound by a propertyfrom doc.object('Space.Class') as obj, Document doc1, doc1.object('Space.Class1') obj1 where obj.prop1 = doc1.fullName and obj1.prop2 like '%text%' order by obj1.prop3TODO
List child spaces (7.2+)select space.name from XWikiSpace as space where space.parent = 'Parent Space'same
List the nested documents that contain 'wiki' in their name (7.2+)from XWikiSpace as space where doc.space = space.reference and ((doc.name <> 'WebHome' and lower(doc.name) like '%wiki%') or (doc.name = 'WebHome' and lower(space.name) like '%wiki%')), XWikiSpace as space where doc.space = space.reference and ((doc.name <> 'WebHome' and lower(doc.name) like '%wiki%') or (doc.name = 'WebHome' and lower(space.name) like '%wiki%'))
List all XWiki groupsselect doc.fullName from Document doc, doc.object(XWiki.XWikiGroups) gselect doc.fullName from XWikiDocument as doc, BaseObject as obj where obj.name = doc.fullName and obj.className='XWiki.XWikiGroups'
List all nested documents in the space "Space"where doc.space like 'Space' or doc.space like 'Space.%'same
Find all user pages that don't have have a XWiki.OIDC.ConsentClass xobject

Since XWQL doesn't support nested selects using the doc.object() syntax, we need to use HQL in the nested select.

from doc.object(XWiki.XWikiUsers) as user where doc.fullName not in (select distinct obj2.name from BaseObject as obj2 where obj2.className = 'XWiki.OIDC.ConsentClass')

TODO

Note that when using XWQL, it's mandatory to use aliases when filtering for multiple properties of an object; it's not possible to use the abbreviated syntax doc.object(Blog.BlogPostClass).propertyName multiple times in the same query. 

See also examples of queries at https://github.com/xwiki/xwiki-platform/blob/master/xwiki-platform-core/xwiki-platform-oldcore/src/main/resources/queries.hbm.xml

Performing Queries

The principle is to get a reference to the Query Manager, then call a method on it to create a Query for a given Query language. Then on the Query object you can set some behaviors (limit the number of returned result, bind variables, set the offset, set the wiki on which to execute the query) and then execute it.

From Velocity

For XWQL:

  • Return all the results matching the XWQL statement:
    $services.query.xwql('xwqlstatement').execute()
  • Return only 5 results at most:
    $services.query.xwql('xwqlstatement').setLimit(5).execute()
  • Set the starting offset (starts at result number 5 and beyond):
    $services.query.xwql('xwqlstatement').setOffset(5).execute()
  • Set the wiki on which to run the query (requires Programming Rights before XWiki 4.1):
    $services.query.xwql('xwqlstatement').setWiki('mywiki').execute()
  • You can add filters to apply to the query and multiple filters can be added to a query.
    • The hidden filter will exclude documents marked as hidden from the query results, except if the user has chosen to see hidden documents in his profile preferences:
      $services.query.xwql('xwqlstatement').addFilter('hidden').execute()
    • The unique filter will make sure you don't get duplicate results with a short-form query:
      $services.query.xwql('xwqlstatement').addFilter('unique').execute()
    • The count filter allows to transform a short-form query into a count of the distinct results it would return. Unfortunately this doesn't work with XWQL on versions older than 4.4.1 and also it will return -1 if doc.fullname column is not selected:
      $services.query.xwql('xwqlstatement').addFilter('count').execute()
      ## You can also use the following shortcut
      $services.query.xwql('xwqlstatement').count()
    • The language filter will transform the query to also return document languages (the result you'll get is List<Object[]> with Object[0] being the document name and Object[1] being the document language, e.g. en, fr, fr_FR, etc): New in 5.1
      $services.query.xwql('xwqlstatement').addFilter('language').execute()
    • The currentlanguage filter will only return documents in the current language: New in 5.1
      $services.query.xwql('xwqlstatement').addFilter('currentlanguage').execute()
  • Binding a LIKE value New in 8.4.5, 9.3RC1:

    You need to be careful about 2 things:

    • Define which part of the parameter you wish to escape. For example if you have a parameter representing a Document reference and you wish to find all children, you'll want to escape the % and _ characters (and even the [ character since on some DB the constructs [specifier] and [^specifier] have a special meaning) from the Document reference but end the parameter value with a % that you don't want to escape.
    • If you wish that your query works on MySQL then you need to be careful to also have the \ character escaped since MySQL considers it as its escape character by default.

    Thus the recommended approach is to do the following:

    $services.query.xwql('xwqlstatement like :ref').bindValue('ref').literal("${documentReference}.").anyChars().query().execute()

    The full API is:

    • literal(): Represents characters on which to perform escaping.
    • anyChar(): Represents the _ character in SQL (i.e. any single character).
    • anyChars(): Represents the % character in SQL (i.e. one or several characters).

    If you wish to bind several parameters at once you should write something like:

    #set ($queryParams = [])
    ...
    #set ($discard = $queryParams.add($services.query.parameter().literal($request.space)))
    ...
    $query.bindValues($queryParams)...
  • Bind a non-LIKE value:
    $services.query.xwql('xwqlstatement containing :var').bindValue('var', 'value').execute()

    Specific example that will return all documents that have comments containing the test string:

    $services.query.xwql('select doc.fullName from Document doc, doc.object(XWiki.XWikiComments) com WHERE doc.translation = 0 and com.comment LIKE :value').bindValue('value', '%test%').execute()

    You can also bind a list or an array to a query parameter: New in 6.0

    #set ($query = $services.query.xwql('where doc.name in (:myList)'))
    $query.bindValue('myList', ['Activity', 'BlogPostClass', 'Welcome']).execute()
  • Behaviors can be chained:
    $services.query.xwql('xwqlstatement').setWiki('mywiki').setOffset(2).setLimit(5).execute()

For HQL:

$services.query.hql('hqlstatement').execute()

The information above is valid starting with XWiki Platform 2.4M2. Before that you had to use the following which is now deprecated:

## For XWQL
$xwiki.queryManager.xwql('xwqlstatement').execute()
## For HQL
$xwiki.queryManager.createQuery('hqlstatement', 'hql').execute()

From other Scripting languages

For XWQL (example with Groovy):

services.query.xwql('xwqlstatement').execute()

For HQL:

services.query.hql('hqlstatement').execute()

From Java components

Get a QueryManager injected. For example :

@Inject
private QueryManager queryManager;

Obtain and execute a query :

this.queryManager.createQuery(statement, Query.XWQL).execute();

Example:

Query query = this.queryManager.createQuery("select doc.space, doc.name, doc.author from XWikiDocument doc, BaseObject obj where doc.fullName=obj.name and obj.className='XWiki.WikiMacroClass'");
List<Object[]> results = (List<Object[]>) (List) q.execute();
for (Object[] wikiMacroDocumentData : results) {
    String space = (String) wikiMacroDocumentData[0];
    String name = (String) wikiMacroDocumentData[1];
    String author = (String) wikiMacroDocumentData[2];
   ...
}

Pitfalls

Querying multiple wikis at once

When using the HQL or XWQL languages you can only query one wiki at a time (using the setWiki() call to set the wiki to query on, see above). The reason is that this does some database query on one schema (we cannot do queries on multiples database schema at once).

However, there's a solution, which is to use the SOLR language, which does queries against a SOLR index and thus on all subwikis.

See the Solr Query Langage documentation for details.

Finding pages under nested pages

You may be tempted to write:

#set ($query = $services.query.xwql('from doc.object(Space.Class) as obj where doc.space like :space'))
#set ($query =  $query.bindValue('space', 'space1.space2With\.Dot.%')
$query.execute()

However this will fail under MySQL (it'll work under HSQLDB though)! The reason is that MySQL considers the \ character as an escape character.

To make this query work on all DBs you need to write:

#set ($query = $services.query.xwql("from doc.object(Space.Class) as obj where doc.space like :space escape '!'"))
#set ($spaceReferenceString = 'space1.space2With\.Dot')
#set ($spaceLike = $spaceReferenceString.replaceAll('([%_!])', '!$1').concat('.%'))
#set ($query = $query.bindValue('space', $spaceLike))
$query.execute()

Note that % and _ are special LIKE characters (means any characters for the former and one character for the latter).

Since XWiki version 8.4.5 or 9.3RC1 you can simplify this to:

#set ($spaceReferenceString = 'space1.space2With\.Dot')
#set ($query = $services.query.xwql("from doc.object(Space.Class) as obj where doc.space like :space"))
#set ($query = $query.bindValue('space').literal("${spaceReferenceString}.").anyChars().query())
$query.execute()

XWQL Implementation Details

The XWQL grammar is defined here.

Note that currently the XWQL implementation translates queries into HQL which are then executed using Hibernate against the RDBMS.

XWiki-specific extensions in XWQL over JPQL

  • Short form queries:
    • where <expr> means select doc.fullName where <expr>
    • from <fromlist> [where <expr>] means select doc.fullName from Document as doc, <fromlist> [where <expr>]
  • Special syntax for XWiki objects in from and where clauses:
    • from doc.object(Class) as obj
    • where doc.object(Class).prop = 'something'

    The class name must be quoted if it contains special characters (e.g. Unicode). Both single and double quotes can be used.

Note that any JPQL query is a correct XWQL query. This also means almost any HQL query (HQL is a JPQL superset) will work with XWQL (when executed on the XWiki Hibernate store).

Noteworthy differences between XWQL and HQL

DescriptionXWQLHQLDetails
 Like statement can't be transformed in XWQL  where upper(doc.name) like 'WEBHOME'  where upper(doc.name) like upper('webhome')  The string_expression must have a string value. The pattern_value is a string literal or a string-valued input parameter

Allowed HQL/XWQL select queries

You need to have programming right to use fully qualified select queries (if you don't specify the select part, select doc.fullname from Document doc is concatenated to the passed query) unless you use only the following columns:

HQL tableXWQL tableColumnSince version
XWikiDocumentDocumentfullName7.2M2
name7.2M2
space7.2M2
language7.2M2
defaultLanguage7.2M2
translation7.2M2
hidden7.2M2
XWikiDeletedDocumentXWikiDeletedDocumentid9.8RC1
fullName9.8RC1
language9.8RC1
XWikiSpaceSpacereference7.2M2
name7.2M2
parent7.2M2
hidden7.2M2
XWikiAttachmentXWikiAttachmentfilename9.7RC1
DeletedAttachmentDeletedAttachmentid9.8RC1
filename9.8RC1

Using the XWiki object (Deprecated)

This is now deprecated in XWiki Enterprise 2.4 in favor of the Query Manager but it's still supported and XWiki's code itself is still using this manner as we're gradually moving to using the Query Manager.

See the Velocity HQL Guide (till we incorporate it back in this guide).

For Developers

Query Filters

It's possible to write code to filter queries. By filtering, we mean the ability to perform modifications on the query statement, parameters and even the returned results from the store.

To write a query filter, create a Component implementing the QueryFilter interface role.

For example:

/**
 * Query filter adding a select clause to also return the document's language. This is useful if you need to adapt
 * your display based on the document's language (for example in search results).
 *
 * @version $Id: d8e8ad0d6721f23c6a34dd209d4a7940c98a02fc $
 * @since 5.1M2
 */

@Component
@Named("language")
@Singleton
public class LanguageQueryFilter implements QueryFilter
{
   /**
     * The select clause to extend in order to also return the document's language.
     */

   private static final String SELECT_CLAUSE_TO_EXTEND = "select doc.fullName";

   /**
     * @param statement statement to filter.
     * @return true if the filter can be applied to the passed statement, false otherwise.
     */

   private boolean isFilterable(String statement)
   {
       return statement.startsWith(SELECT_CLAUSE_TO_EXTEND);
   }

   @Override
   public String filterStatement(String statement, String language)
   {
        String result = statement.trim();

       if (Query.HQL.equals(language) && isFilterable(result)) {
            result = SELECT_CLAUSE_TO_EXTEND + ", doc.language" + result.substring(SELECT_CLAUSE_TO_EXTEND.length());
       }

       return result;
   }

   @Override
   public List filterResults(List results)
   {
       return results;
   }

   @Override
   public Query filterQuery(Query query)
   {
       return query;
   }
}

New in XWiki 9.3RC1 The filterQuery() method now allows to completely configure any part of the Query and since it's more generic it can be used as a replacement for the filterStatement() method.

Here's an example of how to use it to modify the statement passed:

    @Override
   public Query filterQuery(Query query)
   {
       return new WrappingQuery(query)
           {
               @Override
               public String getStatement()
               {
                   return ... modified statement here...
               }
           };
   }
Tags: development
    

Get Connected