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