HQL Query Examples in Velocity

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

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.

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.

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

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: Documents, Objects, Properties, etc)

Warning: Calls to te 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.

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'")

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

Non-exhaustive list of queryable Object Fields

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