Skip to Content

HQL Scripting Examples in Velocity

Version 35.6 by Pascal Bastien on 2019/07/31
Warning: For security reasons, the document is displayed in restricted mode as it is not the current version. There may be differences and errors due to this.
Contents

HQL Query Examples in Velocity

Warning

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)

Failed to execute the [velocity] macro. Cause: [The execution of the [velocity] script macro is not allowed in [xwiki:Documentation.DevGuide.Scripting.velocityHqlExamples.WebHome]. Check the rights of its last author or the parameters if it's rendered from another script.]. Click on this message for details.

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)

Failed to execute the [velocity] macro. Cause: [The execution of the [velocity] script macro is not allowed in [xwiki:Documentation.DevGuide.Scripting.velocityHqlExamples.WebHome]. Check the rights of its last author or the parameters if it's rendered from another script.]. Click on this message for details.

#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)

Failed to execute the [velocity] macro. Cause: [The execution of the [velocity] script macro is not allowed in [xwiki:Documentation.DevGuide.Scripting.velocityHqlExamples.WebHome]. Check the rights of its last author or the parameters if it's rendered from another script.]. Click on this message for details.

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

About

About

Support

Platform

User Guide

Admin Guide

Developer Guide

Projects

XWiki

Extensions

Other

Contribute

Status

Practices

Under the Hood

Get Involved

Get Connected