Master-detail query

[KarelH]

Hello,

i want know it's a way to select master view from one table (e.g. select customer-name from customers) and on click on row with customer run second query (e.g. select * from customer-details where customer-name=:1) and result of second query show in AJS.Dialog box.

[Felix Grund (Scandio)]

Hi Karel,

Ok, I just built this up. I used the sample World MySQL DB with the table Country and the columns Name and Population.

I have one query called EuropeanCountryNames that lists all European country names:

SELECT Name AS country FROM Country WHERE Continent = 'Europe';

Then I have a secondary query called PopulationByCountryName that shows the population of a given country name:

SELECT Name, Population FROM Country WHERE Name = :country;

For the query PopulationByCountryName i use the default template which will simply render a table with one row and two columns for the country name and its population. I add the PocketQuery macro my page CountryPopulation. I checked the macro parameter Enable Dynamic Parameters such that I'll be able to render the page with country names as GET parameters in the URL, e.g. %confluence-path%/pages/viewpage.action?pageId=1234567&pq_country=Italy

The template for my first query EuropeanCountryNames renders a simple list of links of country names. I then add the logic for everything else in a <script> tag in that template:

<ul class="pocketquery-country-list">
#foreach ($row in $result)
	<li><a href="#" data-urlencoded-name="$generalUtil.urlEncode($row.country)">$row.country</a></li>
#end
</ul>

<script>
(function() {
	// The page ID of my population page ID.
	var POPULATION_PAGE_ID = 1638404;

	// Helper function for showing the dialog. The given contentHtml will be the
	// content of the dialog. This will be the rendered PocketQuery template taken
	// from another page.
	function showDialog(contentHtml) {

		var dialog = new AJS.Dialog({
			width: 400,
			height: 300,
			id: 'pocketquery-population-dialog'
		});

		dialog.addHeader('Country Population');
		dialog.addSubmit("OK", function() {
			dialog.remove();
		});

		dialog.addPanel('', '');
		dialog.getCurrentPanel().html(contentHtml);
		dialog.show();
	}

	// When a link in the country list is clicked, we take the url encoded country name and
	// append it as GET parameter "pq_countryname" to the populationPage URL. We can then render the
	// page for this specific country, extract the HTML, create a dialog and use the HTML as content.
	jQuery('.pocketquery-country-list a').on('click', function(e) {
		// This is the page that contains the PocketQuery macro for the query PopulationByCountryName.
		var populationPage = AJS.params.baseUrl + '/pages/viewpage.action?pageId=' + POPULATION_PAGE_ID;
		var urlEncodedCountryName = jQuery(this).data('urlencoded-name');
		var ajaxUrl = populationPage + '&pq_country=' + urlEncodedCountryName;
		e.preventDefault();
		jQuery.get(ajaxUrl, function(populationPageHtml) {
			var tableHtml = jQuery(populationPageHtml).find('.pocketquery-table');
			showDialog(tableHtml);
		});
	});

}());
</script>

This gif shows what it looks like:

pocketquery-ajax-dialog.gif

I think you can set up your implementation along these lines. Or let me know if you need further help .

Felix [Scandio]

[KarelH]

Hi Felix,

thank you for answers. I had a second idea which was same as your update, but you improved it with use JS . Please can you provide me a little showcase as you offered?

Thank you

Karel

[Felix Grund (Scandio)]

Hi Karel,

Very interesting scenario! Ideally, you would send an Ajax request to a PocketQuery REST service for the second query and then render the result in an AJS Dialog. This REST service will be a part of PocketQuery 2.0 which is still under development and will take quite some more time.

With the current version I don't think there's a way to achieve this. I guess you're not developing your own plugin? Did you intend to do the logic with the AJS dialog in a PocketQuery template?

Edit: there might actually be another way:

• For your second query, you have a different Confluence page with a PocketQuery macro for that query in it
• The query must be parameterized by GET parameters ("Enable Dynamic Parameters")
• In the template of the first query, you could implement the JS code for Ajax requests to that second site
• You could then manipulate the result HTML of these requests with JS

I could provide a little showcase if you're interested in this pattern.

Regards, Felix [Scandio]