Velocity enables you to use a number of variables and call methods on specific objects. This article describes the variables and objects available in PocketQuery Templates written in Velocity.

General

$result (List of Map<String,Object>)

The result of the query. This is a List of Maps, e.g.:

[
  {Name: 'Germany', Continent: 'Europe', Region: 'Western Europe'},
  {Name: 'Sweden', Continent: 'Europe', Region: 'Nordic Countries'}
]
CODE

$pocketErrors (List)

A list of errors (if any) that occurred executing the query

$stackTrace (String)

The stack trace of the last error (if any)

$debug (Boolean)

True if debug mode is enabled, false otherwise

$page (Page)

The current Confluence page (or other ContentEntityObject)

$space (Space)

The current Confluence space

$user (User)

The current Confluence user

Macro parameters

$debug (Boolean)

Whether or not the debug option is enabled

$allowGetParams (Boolean)

Whether or not GET parameters, i.e. the option "Enable dynamic parameters", are enabled

$includeChangeTemplate (Boolean)

Whether or not the option "Use change params template" is enabled

$dynamicLoad (Boolean)

Whether or not the option "Load macro dynamically" is enabled

$includeReloadButton(Boolean)

Whether or not the option "Include reload button" is enabled

$disableAutoload (Boolean)

Whether or not the option "Disable autoload" is enabled

Query

$query (Query)

The query object (all information on the query can be read from this object)

$queryName (String)

The name of the query

$queryParameters (Map<String, Object>)

Parameters given to the query

$queryExecutionTime (Long)

Time in milliseconds the database transaction took

$rawResult (Object)

Raw query result

Utils

$date (Date)

Date object, initialized to the time of macro execution

$calendar (Calendar)

Calendar object, initialized to the time of macro execution

$dateFormatter (DateFormatter)

DateFormatter object for Confluence-oriented date formatting

$friendlyDateFormatter (FriendlyDateFormatter)

FriendlyDateFormatter object for Confluence-oriented date formatting

$numberTool (NumberTool)

NumberTool object for number formatting

$math (MathTool)

MathTool object for advanced math operations

$sums (Map<String, Long>)

Contains the summed up values of all columns of the result that contain only numeric values. Each value can be accessed using $sums.get("columnName") or $sums.columnName

Request Parameters

$pocketQueryParameters (Map<String, String>)

Query parameters with potentially overridden PQ-specific request parameters (i.e. request parameters starting with pq_ prefix)

$otherParameters (Map<String, String>)

Request parameters that are not specific to PocketQuery query parameters

  

The PocketQuery Velocity Helper

In addition to the objects above, there is a global Velocity helper object available in PocketQuery templates that contains some utility methods. You can access this helper with the context key $PocketQuery. Below you find a list of the methods that are currently available.

In PocketQuery 4.0 we introduced the concept of query keys for more PQ technical features. Please have a look at PocketQuery 4.x Upgrade Notes if you intend to use this API.

$PocketQuery.executeQuery(String queryKey)

Executes the given query and returns the result set that was fetched from the datasource and post-processed. If a converter is set, it will be executed, but the rendering of the template will be skipped.

This can be useful if you want to combine data from one datasource with another without rendering the whole query.

Note: You have to check for errors yourself when using this. Make use of hasErrors() and printErrors() accordingly.

Example:

#set($myResult = $PocketQuery.executeQuery("f3f307b2-50b9-4549-be2d-5d6b74db7649"))
#if($myResult.hasErrors())
  $myResult.printErrors()
#else
  ## From here on you can use $myResult just like $result
  #foreach ($row in $myResult)
      <div>
      #foreach ($column in $row)
        <span>$!column <span>
      #end
      </div>
    #end
#end
CODE

$PocketQuery.executeQuery(String queryKey, Map<String, Object> queryParameters)

Same as above, but you can also pass query parameters that will be used in the execution of your query. Example:

#set($myResult = $PocketQuery.executeQuery("myQueryName", {
  "MyWord": "Bird",
  "MyNumber": 1
}))
CODE

$PocketQuery.template(String templateKey)

Return the rendered HTML of the template with the given template key while using the data of the current result. This can be useful if you want to display the result of the related query in two different ways.

The template can be:

  • The default PocketQuery template with the key “default”.

  • A PocketQuery template created in PocketQuery admin. The template key will be the UUID of the template.

Examples in Velocity templates:

// Render the query data first as default table and below that using your custom template
$PocketQuery.template("default")
$PocketQuery.template("f3f307b2-50b9-4549-be2d-5d6b74db7649")
CODE

Rendering


$PocketQuery.renderMacro(String name)

Return the rendered HTML of the macro with the given name without macro parameters or any further environment options. Example:

$PocketQuery.renderMacro("cheese")
CODE

$PocketQuery.renderMacro(String name, Map<String, Object> options)

Return the rendered HTML of the macro with the given name using the given map of options. Options are currently:

  • page (ContentEntityObject): the underlying content entity object (mostly page or blogpost) (defaults to null)

  • parameters (Map<String, String>): macro parameters (parameterName => parameterValue) (defaults to an empty map)

  • body (String): macro body (defaults to null)

  • bodytype (String): type of the body string, either “plaintext” or “richtext” (defaults to “richtext”). If “plaintext” is set, the body content is not interpreted.

Example:

$PocketQuery.renderMacro("info", {
  "page": $page,
  "parameters": { "title": "PocketQuery rocks!" },
  "body": "This is an info macro that says 'PocketQuery rocks!'"
})
CODE

$PocketQuery.renderPocketQueryMacro(String queryName)

Return the rendered HTML of the PocketQuery macro using the query with the given query key. The query name is the one defined in PocketQuery admin. Example:

$PocketQuery.renderPocketQueryMacro("f3f307b2-50b9-4549-be2d-5d6b74db7649")
CODE

$PocketQuery.renderPocketQueryMacro(String queryName, Map<String, Object> options)

Return the rendered HTML of the PocketQuery macro using the query with the given query key.
Use the given map of options for further configuration. Options are currently:

  • page (ContentEntityObject): the underlying content entity object (mostly page or blogpost) (defaults to null)

  • parameters (Map<String, String>): parameters for the PocketQuery statement (parameterName => parameterValue) (defaults to an empty map)

  • dynamic load (String): if you want the macro to be rendered in the background, set this to “true”

Example:

$PocketQuery.renderPocketQueryMacro("f3f307b2-50b9-4549-be2d-5d6b74db7649", {
  "page": $page,
  "parameters": { "Continent":"Europe" },
  "dynamicload": "true"
})
CODE

Utils


$PocketQuery.newList()

Create a new List and return it. Example:

$PocketQuery.newList()
CODE

$PocketQuery.newMap()

Create a new Map and return it. Example:

$PocketQuery.newMap() 
CODE

$PocketQuery.newSet()

Create a new Set and return it. Example:

$PocketQuery.newSet()
CODE

$PocketQuery.reverse(Collection<String> collection)

Reverse the order of the given collection and return the result. Example:

$PocketQuery.reverse($myArrayList)
CODE

$PocketQuery.sort(Collection<String> collection)

Sort the given collection alphabetically and return the result. Example:

$PocketQuery.sort($myArrayList)
CODE

$PocketQuery.formatDate(Date date, String formatString)

Formats a given date to a desired format. Uses java.text.SimpleDateformat, so all format strings must be compliant to the specification. Example:

$PocketQuery.formatDate($myDate, "dd.MM.yyyy")
CODE

$PocketQuery.getLocale(String localeString)

Creates a Locale object that can be used with other Velocity APIs, such as the $numberTool. Example:

#set($locale = $PocketQuery.getLocale("pt_BR"))
 
<div>$numberTool.format("currency", 134533454512.153, $locale)</div>
## Output: ¤ 134,533,454,512.15
CODE

$PocketQuery.getRandomUUID()

Generates a random UUID String that can be used to create a unique identifier within a Template.
Useful for Templates that have JavaScript which operates on the Query result and are used multiple times on the same page by the same Query (possibly with different parameters).

$PocketQuery.parseDate(String dateString, String simpleDateFormat)

Parses a string into a date depending on a supplied format to allow for date calculations within Templates. The returned object will be of type java.util.Date. The provided dateString must be a string that matches the format specified with simpleDateFormat. This is specified in java.text.SimpleDateFormat (in particular, the “Examples” section in that documentation is useful). Examples:

#set($pqDate = $PocketQuery.parseDate("11/20/2020", "MM/dd/yyyy"))
Today's Date = $pqDate<br>

#set($pqTime = $PocketQuery.parseDate("2020-11-20 17:11:23", "yyyy-MM-dd HH:mm:ss"))
Today's Time = $pqTime<br>
CODE