Is it possible to use PocketQuery to do nested queries?For example, let's say we have a list of Customers in one table, and a list of Orders for those Customers in another table.I want to be able to have one page where I first query the Orders table for the distinct list of Customers with Orders, then I want to list the Orders for each Customer under a heading for that Customer.Like this:CustomerAOrder 1Order 4Order 6CustomerBOrder 2Order 3 etc.It would only have headings for those Customers that actually had Orders (not all Customers)It wouldn't necessarily need to use bullet points. It would be better if it were tables under each heading.Is this possible? EDIT: I've actually got round this by doing one query which is sorted by the heading values and using the velocity template script to check for changes in the heading value to actually out put the heading text itself.

Nested PocketQuery queries

Is it possible to use PocketQuery to do nested queries?

For example, let's say we have a list of Customers in one table, and a list of Orders for those Customers in another table.

I want to be able to have one page where I first query the Orders table for the distinct list of Customers with Orders, then I want to list the Orders for each Customer under a heading for that Customer.

Like this:

CustomerA

Order 1
Order 4
Order 6

CustomerB

Order 2
Order 3

etc.

It would only have headings for those Customers that actually had Orders (not all Customers)

It wouldn't necessarily need to use bullet points. It would be better if it were tables under each heading.

Is this possible?

EDIT: I've actually got round this by doing one query which is sorted by the heading values and using the velocity template script to check for changes in the heading value to actually out put the heading text itself.

5 answers

1 accepted

3 votes

Answer accepted

Hi Duncan,

This is a more advanced usage of PocketQuery, but it's definitely possible. You'll have to use our Velocity helper method renderPocketQueryMacro in the template of your original query. So you'll have two (or more) queries:

InnerQuery - the actual query that shows the result
OuterQuery - the query running a second PocketQuery macro with the InnerQuery

I'll show you a "most simple" example. I have a query "Continents" that will list me the names of continents with countries that have a population higher than :MinPopulation. This will be the InnerQuery.

Query:

SELECT DISTINCT Continent
FROM Country
WHERE Population &gt; :MinPopulation;

I'll use the default template for this query.

Then I have an OuterQuery that should render this InnerQuery in its template. This is in my example kind of a dummy query that will only pass the parameter :MinPopulation to my inner query. That's why I simply put the parameter in a "empty" SELECT clause:

SELECT :MinPopulation;

Then, I have a custom template for this OuterQuery that should render the PocketQuery macro with the InnerQuery:

$PocketQuery.renderPocketQueryMacro("Continents", {
	"page": $page,
	"parameters": $queryParameters
})

Note that this will simply pass the parameters of the OuterQuery (:MinPopulation) and also the current Confluence page object to the InnerQuery.

You might also want to set the "dynamicload" flag (new PQ 2.0 feature!) to the InnerQuery like this:

$PocketQuery.renderPocketQueryMacro("Continents", {
	"page": $page,
	"parameters": $queryParameters,
	"dynamicload": "true"
})

Hope this helps. Let me know if you have further questions!

Regards, Felix (Scandio)