HQL Scripting Examples in Velocity
Last modified by Manuel Leduc on 2023/02/02
Contents
HQL Query Examples in Velocity
XWiki allows user to access documents and objects with HQL queries in Velocity scripts.
Public API (searchDocuments)
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.
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")
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
#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)
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
#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)
## 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'")
#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
- Most Viewed Articles Snippet
- Most Active Contributors In Group Snippet
- Number Of Active Users Per Day And Per Week Snippet
- Number Of Edited Articles Per Day And Per Week Snippet
- Number Of Created Articles Per Day And Per Week Snippet
- Number Of Deleted Articles Per Day And Per Week Snippet
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
