Velocity HQL query examples

XWiki allows user to access documents and objects with hql queries in Velocity scripts.

Invalid macro parameters used for the "toc" macro. Cause: [Failed to validate bean: [must be greater than or equal to 1]]. Click on this message for details.

Public API (searchDocuments)

With this API the query consist in the WHERE condition. Any user with edit rights can use this API (as in write a script using it). Any user with view rights can view the result of such a query.

You can execute queries as follows:

 * ActiveInstalls.ActiveInstallsYearlyHistoryData
 * ReleaseNotes.Data.XWiki.8\.3M1.Change003.WebHome
 * Documentation.DevGuide.FrontendResources.LiveTable.WebHome
 * XWiki.XWikiSyntaxGeneralRemarks
 * XWiki.XWikiSyntaxOtherSyntaxes

Simple Query

#displayQuery($hql false)

Ordered Query

#displayQuery($hql false)

Advanced Query (date & time)

Since there is no standard way to calculate dates interval in HQL those queries are a bit unnatural.

#displayQuery($hql false)

Other examples, documents modified :

during current day : "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 1) order by doc.date desc"
during current week : "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 7) order by doc.date desc"
during current month : "where year(doc.date) = year(current_date()) and month(doc.date) > (month(current_date()) - 1) order by doc.date desc"

Privileged API (search : Documents, Objects, Properties, etc)

Warning: Calls to te privileged API are only executed when the calling page has been saved by an Admin. The reason is that search can be used to send any HQL command like update, delete, etc.

You can execute queries as follows:

 *  Bug Fixing Day 197  <br/>
 * 002_samcedarmuir  <br/>
 * 02_samcedarmuir  <br/>
 * 114  <br/>
 * 123Ocean  <br/>

Simple Query

#displayQuery($hql true)

Count Query

select count(doc) from XWikiDocument doc result : 8009
select count(doc) from XWikiDocument doc results : 8009


Simple Query with multiple fields

                      Translations : 2020-11-02 13:40:58.0 <br/>
                      ActiveInstallsYearlyHistoryData : 2018-07-16 11:45:31.0 <br/>
                      XWiki Big Data with Elastic Search : 2020-01-28 14:27:17.0 <br/>
                      WebHome : 2019-09-13 10:07:48.0 <br/>
                      WebHome : 2020-11-05 15:23:14.0 <br/>
#displayQuery($hql true)


Getting objects of a specific class

#displayQuery($hql true)


Getting objects' properties

#displayQuery($hql true)


Getting documents where objects' properties equals some value

#displayQuery($hql true)

List users currently editing pages

#displayQuery($hql true)

List attachments of a page

#displayQuery($hql true)

Non-exhaustive list of queryable object fields

XWikiDocument

  • XWikiDocument.fullName : full name, including space and page name. Example of value : Main.WebHome.
  • XWikiDocument.author : last editor. Example of value : XWiki.Admin.
  • XWikiDocument.creator : first editor. Example of value : XWiki.Admin.

BaseObject

  • BaseObject.id : arbitrary unique id of the object. Example of value : 123456789.
  • BaseObject.className : class. Example of value : XWiki.XWikiUsers.

*Property (StringProperty, etc)

  • Property.id.id : unique id of the object the property belongs to. Example of value : 123456789.
  • Property.name : name of the property. Example of value : first_name.
  • Property.value : value. Example of value : John.
Tags: HQL
Created by Vincent Massol on 2017/09/05