Velocity HQL query examples
XWiki allows user to access documents and objects with
hql queries in
Velocity scripts.
Public API (searchDocuments)
With this API the query consist in the WHERE condition.
Any user with edit rights can use this API (as in write a script using it).
Any user with view rights can view the result of such a query.
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:
| Single field |
|---|
| AdminGuide.Authentication |
| Features.RSSFeeds |
| AdminGuide.Configuration |
| AdminGuide.ImportExport |
| Features.IntegratedStatistics |
Ordered Query
xwiki.searchDocuments("where doc.creator='XWiki.VincentMassol' order by doc.date asc", 5, 0) returns:
| Single field |
|---|
| Features.Plugins |
| Features.RSSFeeds |
| DevGuide.CreatingPlugins |
| Features.Applications |
| Features.PortletIntegration |
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)
Warning: Calls to te privileged API are only executed when the calling page has been saved by an Admin.
The reason is that search can be used to send any HQL command like update, delete, 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:
| Single field |
|---|
| Access Rights |
| AccountValidation |
| AddClassProperty |
| AddObject |
| AdminExportSheet |
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:
| field 1 | field 2 | |
|---|
| XWikiUsers | 2008-02-29 17:19:17.0 | |
| XWikiGroups | 2007-04-20 19:07:36.0 | |
| XWikiRights | 2006-12-15 11:10:41.0 | |
| XWikiComments | 2006-12-15 11:10:42.0 | |
| XWikiSkins | 2006-12-31 15:47:36.0 | |
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:
| Single field |
|---|
| xwiki:XWiki.VincentMassol |
| xwiki:XWiki.astrapi |
| xwiki:XWiki.jvdrean |
| xwiki:XWiki.ThomasMortagne |
| xwiki:XWiki.LudovicDubost |
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:
| Single field |
|---|
| xwiki-product.png |
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.