Master-detail query

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.

3 answers

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:

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

&lt;script&gt;
(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 + '&amp;pq_country=' + urlEncodedCountryName;
		e.preventDefault();
		jQuery.get(ajaxUrl, function(populationPageHtml) {
			var tableHtml = jQuery(populationPageHtml).find('.pocketquery-table');
			showDialog(tableHtml);
		});
	});

}());
&lt;/script&gt;

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 smile.

Felix [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]

Hi Felix,

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

Thank you

Karel

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Bridget Sauer
Published Thursday in Marketplace Apps

Calling all developers––You're invited to Atlas Camp 2018

 Atlas Camp   is our developer event which will take place in Barcelona, Spain  from the 6th -7th of   September . This is a great opportunity to meet other developers and get n...

88 views 0 5
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