Velocity HQL query examples

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

Public API (searchDocuments)

You can execute queries as follows:

#set($query="where doc.creator='XWiki.VincentMassol'")
#set($results = $xwiki.searchDocuments($query, 5, 0))
#foreach ($item in $results)
 * $item
#end

Simple Query

xwiki.searchDocuments("where doc.creator='XWiki.VincentMassol'", 5, 0) returns:

Ordered Query

xwiki.searchDocuments("where doc.creator='XWiki.VincentMassol' order by doc.date asc", 5, 0) returns:

Advanced Query (date & time)

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

xwiki.searchDocuments("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", 5, 0) returns:

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)

You can execute queries as follows:

#set($query="select doc.name from XWikiDocument doc")
#set($results = $xwiki.search($query, 5, 0))
#foreach ($item in $results)
 * $item  <br/>
#end

Simple Query

xwiki.search("select doc.name from XWikiDocument doc", 5, 0) returns:

Count Query

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

select count(doc) from XWikiDocument doc results : 271

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

xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0) returns:

Getting objects of a specific class

xwiki.search("select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'", 5, 0) returns:

Getting objects' properties

xwiki.search("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'", 5, 0) returns:

Getting documents where objects' properties equals some value

xwiki.search("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'", 5, 0) returns:

List users currently editing pages

xwiki.search("select distinct lock.userName from XWikiLock lock", 5, 0) returns:

List attachments of a page

xwiki.search("select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id", 5, 0) returns:

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.