HQL Scripting Examples in Velocity

Last modified by Manuel Leduc on 2023/02/02

HQL Query Examples in Velocity

The examples on this page are using deprecated search APIs ($xwiki.searchDocuments and $xwiki.search). If you want to query the XWiki database then you should use the Query Module instead. If you want to perform a full text search on the XWiki model then you can use the Solr Search API.

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

Public API (searchDocuments)

With this API the query consist in the WHERE condition of a full HQL query. Any user with edit rights can write a script using this API. Any user with view rights can view the result of such a query.

General example showing how to display the first 5 results of a given query:

#set($hql = "<query here>")
#set($results = $xwiki.searchDocuments($hql, 5, 0))
#foreach ($item in $results)
 * $item
#end

The examples below will show you various HQL queries that you can write.

Simple Query

Displays all documents who have been created by the user XWiki.JohnDoe:

#set($hql = "where doc.creator='XWiki.JohnDoe'")

Ordered Query

Displays all documents who have been created by the user XWiki.JohnDoe and sorted by document's last modification date, in ascending order:

#set($hql = "where doc.creator='XWiki.VincentMassol' order by doc.date asc")

Advanced Query (date & time)

Since there is no standard way to calculate dates interval in HQL those queries are a bit unnatural.
#set($hql = "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) = day(current_date()) and hour(doc.date) > (hour(current_time()) - 1)  order by doc.date desc")

Query documents older than 3 days

The following snippet deletes all documents in the space TempDocs that are older than 3 days.
For the computation of the relative age, a calendar object created by the $datetool is used and passed to the hibernate query. Tested with MySQL only.

#set($hql="select doc.fullName from XWikiDocument as doc where doc.web = ? and doc.creationDate < ? ")
#set($cal = $datetool.systemCalendar)$cal.add(7,-3)
#set($olderTempDocs=$xwiki.search($hql,5000, 0, ["TempdDocs",$datetool.toDate($cal)]))
#foreach($x in $olderTempDocs)$xwiki.getDocument($x).deleteWithProgrammingRights()#end

Other examples:

  • Listing all documents modified during the 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
  • Listing all documents modified during the 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
  • Listing all documents modified during the 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)

Warning: Calls to the privileged API are only executed when the calling page has been saved by a user with Programming Rights. The reason is that search can be used to send dangerous HQL command like update, delete, etc.

General example showing how to display the first 5 results of a given query:

#set($hql = "<query here>")
#set($results = $xwiki.search($hql, 5, 0))
#foreach ($item in $results)
 * $item
#end

The examples below will show you various HQL queries that you can write.

Simple Query

#set($hql = "select doc.name from XWikiDocument doc")

Count Query

#set($results = $xwiki.search("select count(doc) from XWikiDocument doc"))
## Since $xwiki.search is returning a list, we get its first element
Count : $results.get(0)

Simple Query with multiple fields

#set($results = $xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0))
#foreach ($row in $results)
  #foreach ($col in $row)
    #if ($velocityCount == 1)
      #set($docName = $col)
    #elseif ($velocityCount == 2)
      #set($docDate = $col)
    #end
  #end
  $docName : $docDate <br/>
#end

Getting objects of a specific class

#set($hql = "select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'")

Getting objects properties

#set($hql = "select obj.name, prop.value from BaseObject obj, StringProperty prop where obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name'")

Getting documents where objects properties equals some value

#set($hql = "select doc.fullName from XWikiDocument doc, BaseObject obj, StringProperty prop where doc.fullName=obj.name and obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name' and prop.value='Jean-Vincent'")
#set($hql = ", BaseObject as obj, StringProperty as firstName, StringProperty as lastName where doc.fullName = obj.name and obj.className='XWiki.XWikiUsers' and obj.id=firstName.id.id and firstName.id.name='first_name' and obj.id=lastName.id.id and lastName.id.name='last_name' and firstName.value like 'A%' and lastName.value like 'B%' order by doc.fullName asc")
#set($hql = "select doc.fullName from XWikiDocument doc, BaseObject obj, StringProperty prop where doc.fullName=obj.name and obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name' and prop.value='Jean-Vincent'")

If property is multiselect Page type, relational storage must be checked, if not, the content is not stored the same way and we cannot do "join prop.list as list"

#set ($hql ="select distinct doc.fullName from XWikiDocument as doc, BaseObject as obj, DBStringListProperty as prop join prop.list list where obj.name=doc.fullName and obj.className='MySpace.MyClass' and obj.id=prop.id.id and prop.id.name='MyPropertyName' and list = 'MyValueInMyPropertyName'")

List users currently editing pages

#set($hql = "select distinct lock.userName from XWikiLock lock")

List attachments of a page

#set($hql = "select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id")

Statistics

Non-exhaustive list of queryable Object Fields

The full list of available fields can be found in the Hibernate mapping files (*.hbm.xml), which can be found inside WEB-INF/lib/xwiki-core-x.y.jar (where x.y is the XWiki version).

XWikiDocument

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

BaseObject

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

*Property (StringProperty, IntegerProperty, etc)

  • Property.id.id : unique id of the object the property belongs to. Example value: 123456789
  • Property.name : name of the property. Example value: first_name
  • Property.value : value. Example value: John
Tags: HQL
   

Get Connected