Nested PocketQuery queries

[Duncan Kinnear]

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.

[Felix Grund (Scandio) · Accepted solution]

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:

1. InnerQuery - the actual query that shows the result
2. 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 > :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)

[Duncan Kinnear]

Just updated now. I had a quick read through your page with the new features, and I'm not sure how they relate to the nested query use case.

[Felix Grund (Scandio)]

Yes, that's possible. First the question: did you already update to the recently released new version 2.0? Because this will be much easier with this version. I'll explain you how it works.

[Duncan Kinnear]

Yes, I'd like to run one query that gives all the Customers that had Orders in the last month, say, then for each customer get the list of Orders for that Customer in that month. Can I create a PQ template that has a PQ macro in it to the sub-query?

[Felix Grund (Scandio)]

Hi Duncan! Do I understand you right that you want to run a query with PocketQuery and then take the results of that query and pass something to another query?