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
Accepted answer

 

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)

 

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?

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?

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.

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.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Nov 29, 2018 in New to Confluence

How to use Confluence Cloud for stakeholder management

Most of us don’t need much convincing that stakeholder management is important. It just makes sense that keeping everyone in-the-know on projects and assigning clearly defined roles is key to having ...

1,202 views 4 6
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you