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

 

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
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Jul 10, 2018 in Confluence

We want to see the templates you've created in Confluence!

Hi Community, Jessica here from the Confluence Product Marketing team!  July’s community challenge is all about sharing pictures  — and as an extension of our first post on what ...

785 views 23 12
Join discussion

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