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

This widget could not be displayed.

 

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)

 

This widget could not be displayed.

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?

This widget could not be displayed.

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?

This widget could not be displayed.

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.

This widget could not be displayed.

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
Posted Sep 17, 2018 in Confluence

Why start from scratch? Introducing four new templates for Confluence Cloud

Hi my Community friends!  For those who don't know me, I'm a product marketer on the Confluence Cloud team - nice to meet you! For those of you who do, you know that I've been all up in your Co...

569 views 7 6
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