velocityHqlExamples

Version 29.2 by Danilo Oliveira on 2014/07/17
Warning: For security reasons, the document is displayed in restricted mode as it is not the current version. There may be differences and errors due to this.

HQL Query Examples in Velocity

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

Public API (searchDocuments)

Failed to execute the [velocity] macro. Cause: [The execution of the [velocity] script macro is not allowed in [xwiki:Documentation.DevGuide.Scripting.velocityHqlExamples.WebHome]. Check the rights of its last author or the parameters if it's rendered from another script.]. Click on this message for details.

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.

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)

Failed to execute the [velocity] macro. Cause: [The execution of the [velocity] script macro is not allowed in [xwiki:Documentation.DevGuide.Scripting.velocityHqlExamples.WebHome]. Check the rights of its last author or the parameters if it's rendered from another script.]. Click on this message for details.

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

Failed to execute the [velocity] macro. Cause: [The execution of the [velocity] script macro is not allowed in [xwiki:Documentation.DevGuide.Scripting.velocityHqlExamples.WebHome]. Check the rights of its last author or the parameters if it's rendered from another script.]. Click on this message for details.

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

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)

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

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

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
Tags: HQL
   

Get Connected