General

Query result

$result
TEXT

The variable $result contains the result from your Converter.

To learn more about result formatting, refer to Writing your own PocketQuery Templates and Writing your own PocketQuery Converters.

Example

Result passed from Converter

{
  a: 1,
  b: 2
}
CODE

Template

<p>$result</p>
#foreach($elem in $result.entrySet())
  <p>$elem.key: $elem.value</p>
#end
HTML

Result

{a=1,b=2}
a: 1
b: 2
CODE

Query Parameters

$queryParameters
TEXT

Allows you to access the parameter values passed to your Query. If your Query statement makes use of wildcards, you can also find their values in this Map.

Example

Query Statement

SELECT *
FROM table
WHERE x = :parameter
AND y = :@space.key
CODE

Template

<p>$queryParameters</p>
<p>$queryParameters.get("parameter")</p>
<p>$queryParameters.get("@space.key")</p>
CODE

Result

{parameter=VALUE, @space.key=SPACE}
VALUE
SPACE
CODE

Template insertion by Template ID

$PQ.template(
  templateId
)
CODE

templateId: string

Renders another Template inside the caller Template.

It requires a Template ID, which is a unique identifier of the template you want to parse (you can obtain it in the Template administration). You can call $PQ.template("default") to render the default Template.

Warning: For each Query execution, $PQ.template and $PQ.templateByName can be called to a maximum of 15 times combined.

Example

Template 1 (main)

<p>I am the first template</p>
$PQ.template("cd789c83-e523-0ad1-ba66-61pl84c85f11")
HTML

Template 2 (called)

<p>I am the second template</p>
CODE

Result

I am the first template
I am the second template
TEXT

Template insertion by Template name

$PQ.templateByName(
  templateName
)
TEXT

templateName: string

Renders another Template inside the caller Template.

It requires a Template name, which can change over time. Please, use this method only in the case you are sure the name won’t change. Otherwise, use $PQ.template(templateId). You can also call $PQ.template("default") to obtain the default template.

Warning: For each Query execution, $PQ.template and $PQ.templateByName can be called to a maximum of 15 times combined.

Example

Template 1 (main)

<p>I am the first template</p>
PQ.templateByName("Template 2")
HTML

Template 2 (called)

<p>I am the second template</p>
HTML

Result

I am the first template
I am the second template
TEXT

Macro execution

$PQ.renderPocketQueryMacro(
  queryId,
  parameters?
)
TEXT

queryId: string
parameters: map (optional)

Executes another Query (including its Converter and Template) and returns the rendered result (as a Velocity template code). If you are looking to just get the raw result of another Query, use $PQ.executeQuery instead.

It requires 1 parameter: queryId, which is the ID of the Query to be executed (it can be obtained in the Query administration). The second argument is required in the case where the executed Query contains parameters. In that case, these parameters must be provided.

Example

$PQ.renderPocketQueryMacro("er989c83-e563-12d1-hj23-61pl84c85f56")
$PQ.renderPocketQueryMacro("81270amm-9ij6-kms2-l298-91p78s9852pl", {
  "parameters": {
    "country": "Germany",
    "city": "Munich"
  }
})
HTML

Query execution

$PQ.executeQuery(
  queryId,
  parameters?
)
TEXT

queryId: string
parameters: map (optional)

Executes another Query and returns the result (without applying Converter or Template). This allows you to work with the result of another Query within your Template. If you are looking to directly render another Query, use $PQ.renderPocketQueryMacro instead.

It requires 1 parameter: queryId, which is the ID of the Query to be executed (it can be obtained in the Query administration). The second argument is required in the case where the executed Query contains parameters. In that case, these parameters must be provided.

Example

#set($myResult = $PQ.executeQuery("f3f307b2-50b9-4549-be2d-5d6b74db7649"))

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

Locale (localization)

$PQ.getLocale(
  languageKey,
  countryKey?
)
CODE

languageKey: string
countryKey: string (optional)

Returns a ‘Locale’ (an object containing localization details) based on required country and language. This object is required for some other helpers to adjust their results to your location and language.

It requires 1 parameter: language key. If you want to be more specific (suitable for english-speaking countries), you can also optionally provide country key parameter.

The languageKey parameter refers to language identifier according to RFC-4646/4647.
The countryKey parameter is a country code according to ISO-3166 standard.

Example

#set ($czechLocale = $PQ.getLocale("cs", "CZ"))
#set ($germanLocale = $PQ.getLocale("de", "DE"))
#set ($canadianLocaleEn = $PQ.getLocale("en", "CA"))
#set ($canadianLocaleFr = $PQ.getLocale("fr", "CA"))

<p>$czechLocale.getDisplayCountry(): $czechLocale.getDisplayLanguage()</p>
<p>$germanLocale.getDisplayCountry(): $germanLocale.getDisplayLanguage()</p>
<p>$canadianLocaleEn.getDisplayCountry(): $canadianLocaleEn.getDisplayLanguage()</p>
<p>$canadianLocaleFr.getDisplayCountry(): $canadianLocaleFr.getDisplayLanguage()</p>
HTML

Result

Czech Republic: Czech
Germany: German
Canada: English
Canada: French
TEXT

Random UUID

$PQ.randomUUID()
CODE

Generates a random UUID String that can be used to create a unique identifier within a Template.
This is useful for Templates with JavaScript operating on the Query result, in cases where a Template is executed multiple times on the same page. This might be the case, for example, when a Query is used multiple times on a page with possibly different Query parameters.

Date/Time Formatting

Format date and time

$PQ.formatDate(
  timestamp, 
  format,
  timezone?
)
TEXT

timezone: string
timestamp: number
format: string

Formats a date (as Unix Timestamp) into a human-readable format.

It accepts 2 parameters: timestamp, date format, and optionally timezone.
The timestamp parameter is a Unix timestamp in milliseconds.
The date format parameter is a date-time pattern, corresponding to date format standards.
The timezone parameter is a Time Zone in GMT format (e.g. “GMT”, “GMT-2”, “GMT+6”…), default is “GMT”.

Example

<p>$PQ.formatDate(1615973113000, "yyyy-MM-dd HH:mm:ss", "GMT")<p>
<p>$PQ.formatDate(1615973113000, "yyyy-MM-dd HH:mm:ss", "GMT+8")<p>
<p>$PQ.formatDate(1615973113000, "yyyy-MM-dd HH:mm:ss", "GMT+2")<p>
<p>$PQ.formatDate(1615973113000, "yyyy-MM-dd HH:mm:ss", "GMT-10")<p>
HTML

Result

2021-03-17 09:25:13
2021-03-17 17:25:13
2021-03-17 11:25:13
2021-03-16 23:25:13
CODE

Parse date and time

$PQ.parseDate(
  dateAsString, 
  format,
  timezone?
)
TEXT

timezone: string
dateAsString: string
format: string

Converts a string representation of a date into a Unix Timestamp.

It requires 2 parameters: string representation of date, date format, and optionally timezone.

The date parameter is a date in string format.
The date format parameter is a date-time pattern, corresponding to date format standards.
The timezone parameter is a Time Zone in GMT format (e.g. “GMT”, “GMT-2”, “GMT+6”…), default is “GMT”.

Example

<p>$PQ.parseDate("2021-03-17 09:25:13","yyyy-MM-dd HH:mm:ss", "GMT")</p>
<p>$PQ.parseDate("2021-03-17 17:25:13","yyyy-MM-dd HH:mm:ss", "GMT+8")</p>
<p>$PQ.parseDate("2021-03-17 11:25:13","yyyy-MM-dd HH:mm:ss", "GMT+2")</p>
<p>$PQ.parseDate("2021-03-16 23:25:13","yyyy-MM-dd HH:mm:ss", "GMT-10")</p>
HTML

Result

1615973113000
1615973113000
1615973113000
1615973113000
TEXT

Localized calendar

$PQ.getCalendar(
  locale,
  timezone
)
CODE

locale: localization
timezone: string

Returns a new localized java.util.calendar instance.

It requires 2 parameters: localization and timezone.

The locale parameter is a Locale object described above.
The timezone parameter is a Time Zone in GMT format (e.g. “GMT”, “GMT-2”, “GMT+6”…).

Example

#set ($canadianLocaleEn = $PQ.getLocale("en", "CA"))
#set ($localizedCalendarCa = $PQ.getCalendar($canadianLocaleEn, "GMT-3"))
<p>$localizedCalendarCa.getTimeZone().getID()</p>
HTML

Result

GMT-03:00
TEXT

Text and Currency Formatting

Currency formatter

$PQ.formatCurrency(
  locale, 
  amount
)
TEXT

locale: localization
amount: number

Formats a number as a currency.

It requires 2 parameters: localization and amount.

The locale parameter is a Locale object described above.
The amount is a number (format long, double, or int), which will be formatted.

Example

#set ($czechLocale = $PQ.getLocale("cs", "CZ"))
#set ($germanLocale = $PQ.getLocale("de", "DE"))
#set ($canadianLocaleEn = $PQ.getLocale("en", "CA"))
#set ($canadianLocaleFr = $PQ.getLocale("fr", "CA"))

<p>$PQ.formatCurrency($czechLocale, 10.0)</p>
<p>$PQ.formatCurrency($germanLocale, 10.0)</p>
<p>$PQ.formatCurrency($canadianLocaleEn, 10.0)</p>
<p>$PQ.formatCurrency($canadianLocaleFr, 10.0)</p>
HTML

Result

CZK 10.00
10,00 €
$10.00
10,00 $
TEXT

Markdown formatter

$PQ.markdown(
  markdownText
)
CODE

markdownText: string

Formats Markdown text into HTML.

1 parameter is required: the Markdown text to be formatted.

Example

<p>$PQ.markdown("## Greetings")</p>
<p>$PQ.markdown("**Hello!!** I am greeting you")</p>
HTML

Result (user sees rendered markup)

<p><h2>Greetings</h2></p>
<p><strong>Hello!!</strong> I am greeting you</p>
HTML

Collection Operations

New List

$PQ.newList(
  items?
)
TEXT

items: enumeration (optional)

Creates a new list of items.

It accepts an enumeration of list items. The number of items is not limited. On the returned list, you can call standard Java lists methods.

Example

#set ($listOfNumbers = $PQ.newList(51, 22, 63, 42))
#set ($listOfStrings = $PQ.newList("ab", "cd", "ac", "ca"))
#set ($listOfObjects = $PQ.newList({"a": 12}, {"a": 90}, {"a": 63}, {"a": 8}))

<p>List of Numbers is empty: $listOfNumbers.isEmpty(). Items: $listOfNumbers</p>
<p>List of Strings has $listOfStrings.size() items, the first is: '$listOfStrings.get(0)'</p>
<p>Attributes of objects in ListOfObject are:</p>
<ul>
#foreach($elem in $listOfObjects)
  <li>$elem.a</li>
#end
</ul>
HTML

Result

List of Numbers is empty: false. Items: [51, 22, 63, 42]
List of Strings has 4 items, the first is: 'ab'
Attributes of objects in ListOfObject are:
12 90 63
TEXT

New list of unique items (Set)

$PQ.newSet(
  items?
)
TEXT

items: enumeration (optional)

Creates a new set of items. A set is a special type of list, containing only unique items.

It accepts an enumeration of list items. The number of items is not limited. On the returned set, you can call standard Java lists methods.

Example

#set ($setOfNumbers = $PQ.newSet(51, 67, 23, 10))
#set ($setOfStrings = $PQ.newSet("ab", "cd", "ab", "ca"))

<p>Set of numbers is empty: $setOfNumbers.isEmpty(). Has $setOfNumbers.size() unique elements.</p>
<p>Set of strings has the follwoing elements: $setOfStrings. The first is '$setOfStrings.get(0)'.</p>
HTML

Result

Set of numbers is empty: false. Has 4 unique elements.
Set of strings has the follwoing elements: ab, cd, ca. The first is 'ab'.
TEXT

New Map

$PQ.newMap()
TEXT

Creates an empty map.

A map is a collection of key:value attributes (such as a JSON object) called entries. You can use it to create custom objects in the Velocity template. On the created map, you can call standard Java methods.

Example

#set ($map = $PQ.newMap())
#set ($result = $map.put("a", 23))
#set ($result = $map.put("b", 41))

<p>Map has $map.size() items:</p>
#foreach($entry in $map.entrySet())
  <p>$entry.key: $entry.value</p>
#end
HTML

Result

Map has 2 items:
a: 23
b: 41
CODE

Object to flat list

$PQ.toList(
  object
)
CODE

object: object

Creates a List out of any Object, flattening nested objects inside.

The Default Template uses this function to make a list out of the result. This allows the result to always be displayed as a table, no matter how complex it is.

Example

#set($nestedObject1 = {"a": {"1": 123, "2": 3, "3": 98}, "b": {"1": 432, "3": 13}})
<p>$PQ.toList($nestedObject1)</p>
HTML

Result

[{a.1=123, a.2=3, a.3=98, b.1=432, b.3=13}]
TEXT

Object flattening

$PQ.toFlatMap(
  object
)
CODE

object: object

Flattens an object. Nested object's parameters are appended to the parent object’s keys.

Example

#set($nestedObject2 = {"a": {"1": 123, "2": 3, "3": 98}, "b": {"1": 432, "3": 13}})
<p>$PQ.toFlatMap($nestedObject2)</p>
HTML

Result

{a.1=123, a.2=3, a.3=98, b.1=432, b.3=13}
TEXT

Make list items unique

$PQ.makeUnique(
  list
)
CODE

list: list

Converts a collection (list/set/array) to a set (a list containing unique items only).

Example

#set($nonUniqueList = [1, 45, 2, 2, 45])
#set($uniqueList = $PQ.makeUnique($nonUniqueList))

<p>Before: $nonUniqueList</p>
<p>After: $uniqueList</p>
HTML

Result

Before: [1, 45, 2, 2, 45]
After: [1, 2, 45]
CODE

Sort list

$PQ.sort(
  list,
  compareBy?
)
CODE

list: list
compareBy: string

Sorts the provided list so its items are in an ascending oder.

There are two ways to sort the list. You can either use the default comparator (for non-structured items). If the list contains structures (objects/maps), you can sort them using some particular parameter.

Example

#set($structuredList = [{"a": 5}, {"a": 1}, {"a": 3, "b": 4}])
#set($unstructuredList1 =[2,3,5,1])
#set($unstructuredList2 = ["hello", "ahoj", "hallo"])

<p>Unsorted: $structuredList, sorted: $PQ.sort($structuredList, "a")</p>
<p>Unsorted: $unstructuredList1, sorted: $PQ.sort($unstructuredList1)</p>
<p>Unsorted: $unstructuredList2, sorted: $PQ.sort($unstructuredList2)</p>
HTML

Result

Unsorted: [{a=5}, {a=1}, {a=3, b=4}], sorted: [{a=1}, {a=3, b=4}, {a=5}]
Unsorted: [2, 3, 5, 1], sorted: [1, 2, 3, 5]
Unsorted: [hello, ahoj, hallo], sorted: [ahoj, hallo, hello]
TEXT

Reverse list

$PQ.reverse(
  list
)
CODE

list: list

Reverses the order of items in the provided list.

Example

#set($originalList =[2,3,5,1])
#set($reversedList = $PQ.reverse($originalList))

<p>Original list items: $originalList</p>
<p>Reversed list items: $reversedList</p>
HTML

Result

Original list items: [2, 3, 5, 1]
Reversed list items: [1, 5, 3, 2]
TEXT

Java Utils

PocketQuery provides some standard Java Utils. You can use them as standard Java objects and call their methods to format and convert your query results. Note that these utils are not available under the $PQ or $PocketQuery namespace, but under the global namespace..

Date (java.util)

$date
CODE

Returns a new java.util.Date instance.

You can call $date.getTime() to obtain current Unix Timestamp. The result can be formatted using Format date and time helper, which will help you adjust a result to your time zone (by default, CET is considered).

Example

#set($currentTimestamp = $date.getTime())
#set($fancyCurrentDate = $PQ.formatDate("GMT+1", $currentTimestamp, "yyyy-MM-dd HH:mm:ss"))
<p>Current timestamp is: $currentTimestamp. Or simply $date.<p>
<p>It is $fancyCurrentDate in my time zone!</p>
HTML

Result

Current timestamp is: 1616054544210. Or simply Thu Mar 18 09:02:24 CET 2021.
It is 2021-03-18 09:02:24 in my time zone!
TEXT

Number tool (org.apache.velocity.tools.generic)

$numberTool
CODE

Returns a new org.apache.velocity.tools.generic.NumberTool instance.

The default currency formatting is euro. Navigate to Currency formatting to see other formatting options.

Example

#set($testDouble = 10.05)
<p>Integer of $testDouble is $numberTool.integer($testDouble)</p>
<p>Or it is '$numberTool.currency($testDouble)' as currency.</p>
HTML

Result

Integer of 10.05 is 10
Or it is 'EUR10.05' as currency.
TEXT

Math tool (org.apache.velocity.tools.generic)

$mathTool
CODE

Returns a new org.apache.velocity.tools.generic.MathTool instance.

This instance can be use for basic mathematical operations, such as finding the average or maximum of some numbers.

Example

#set($testInt1 = 10.05)
#set($testInt2 = 20.05)

<p>From $testInt1 and $testInt2 is $mathTool.max($testInt1, $testInt2) greater</p>
<p>Average is $mathTool.getAverage([$testInt1, $testInt2]).</p>
<p>Addition is $mathTool.add($testInt1, $testInt2).</p>
<p>Multiplication is $mathTool.mul($testInt1, $testInt2).</p>
HTML

Result

From 10.05 and 20.05 is 20.05 greater
Average is 15.05.
Addition is 30.1.
Multiplication is 201.50250000000003.
TEXT

Link tool (org.apache.velocity.tools.generic)

$linkTool
CODE

Returns a new org.apache.velocity.tools.generic.LinkTool instance.

This tool helps you to build URLs.

Example

$linkTool.relative('foobar.html').param('id','25').anchor('section4')
HTML

Result

foobar.html?id=25#section4
CODE

Escape tool (org.apache.velocity.tools.generic)

$escapeTool
CODE

Returns a new org.apache.velocity.tools.generic.EscapeTool instance.

This tool helps render tags and code snippets correctly.

Example

<p>Not escaped: <root name='name'><elem></elem></root></p>
<p>Escaped: $escapeTool.xml("<root name='name'><elem></elem></root>")</p>
HTML

Result

Not escaped: 
Escaped: <root name='name'><elem></elem></root>
CODE

Calendar (java.util)

$calendar
CODE

Returns a new default java.util.calendar instance.

The localization of the instance is default (CET). If you want to adjust the instance to your location, please, navigate to Localized calendar helper.

Example

<p>$calendar.getTimeZone()</p>
HTML

Result

sun.util.calendar.ZoneInfo[id="Europe/Berlin",offset=3600000,dstSavings=3600000,useDaylight=true,transitions=143,lastRule=java.util.SimpleTimeZone[id=Europe/Berlin,offset=3600000,dstSavings=3600000,useDaylight=true,startYear=0,startMode=2,startMonth=2,startDay=-1,startDayOfWeek=1,startTime=3600000,startTimeMode=2,endMode=2,endMonth=9,endDay=-1,endDayOfWeek=1,endTime=3600000,endTimeMode=2]]
TEXT