Hi, I have limited experience with templating and SQL so looking for some guidance. I'm attempting to populate cascading select lists using values from an MS SQL database. I'm querying two tables. The first table lists the software platform name, i.e Windows Server and the second table lists the version, i.e. 2008 R2. Using my query and the code below, I've been able to successfully populate the "Platform" and "Version" select lists, but my Platform list has repeating values. How do I specify a one to many relationship in my template so only one instance of the platform is listed? Example: image2016-8-31 10:39:41.png Also, there's no dependency between the two lists. (no cascading functionality) I'd like to be able to select a platform and only have the versions associated with that platform listed in the "Version" select list. Choose Platform: #foreach ($platform in $result) $platform.platform #end Choose Version: #foreach ($version in $result) $version.version #end $PocketQuery.renderPocketQueryMacro("Result_Query", { 'page': $page, 'parameters': {'Platform':'.NET Framework'}, 'parameters': {'Version':'1'}, 'dynamicload': true }) jQuery('#my-pocketquery-form select').change(function() { var container = jQuery('.pocketquery-dynamic-load[data-query="Result_Query"]'); var dataIndex = container.data('index'); var data = PocketQuery.getDynamicLoadData(dataIndex); data.queryParameters = { VersionPlatform: jQuery('#select-platform').val() }; data.queryParameters = { VersionPlatform: jQuery('#select-version').val() }; PocketQuery.load({ container: container, data: data }); }); $PocketQuery.template("default") Thanks, Dan image2016-8-31 10:39:41.png image2016-9-10 12:8:40.png image2016-9-10 12:11:29.png screen-pq.gif

Hi Dan, I tried to reproduce your issue with the MySQL Sample World DB: image2016-9-10 12:8:40.png It should come very close to your scenario. I did it this way: * select box for distinct languages * select box for countries that changes when language in (1) is changed * main table that queries the two tables with country and language as parameters I have 3 queries: 1) Languages: SELECT DISTINCT Language FROM CountryLanguage ORDER BY Language ASC 2) Countries: SELECT Name as Country FROM Country, CountryLanguage WHERE Country.Code = CountryLanguage.CountryCode AND Language = :Language ORDER BY Name ASC 3) CountryByNameAndLanguage SELECT Name, Language FROM Country, CountryLanguage WHERE Country.Code = CountryLanguage.CountryCode AND Name = :Country AND Language = :Language Now comes the tricky part. All three macros in one page or one macro renders other macros from within its template? There are different ways you can achieve what you want. Each requires programming with Velocity and JS. I used this approach: the template for the Languages query is the master and renders the two other queries. Also, I created a separate FormTemplate that is used by Languages AND Countries so we have no duplicate code. This template is included in the other templates with $PocketQuery.template("FormTemplate"). Before it is included, parameters must be set. See my following examples. The query CountryByNameAndLanguage uses the default template. These are my templates... FormTemplate (not used directly by any query, but included with $PocketQuery.template) #set($pqTypeLower = $pqType.toLowerCase()) Choose $pqType: #foreach ($row in $result) $row.get($pqType) #end LanguagesSelect (used by Languages query) #set($pqType = "Language") #set($pqInitialValue = "Swedish") $PocketQuery.template("FormTemplate") $PocketQuery.renderPocketQueryMacro("Countries", { "page": $page, "parameters": {"Language":"Swedish"}, "dynamicload": "true" }) $PocketQuery.renderPocketQueryMacro("CountryByNameAndLanguage", { "page": $page, "parameters": {"Country":"Sweden", "Language":"Swedish"}, "dynamicload": "true" }) (function() { function reload(queryName, queryParameters) { var container = $('.pocketquery-dynamic-load[data-query="'+queryName+'"]'); var dataIndex = container.data('index'); var data = PocketQuery.getDynamicLoadData(dataIndex); data.queryParameters = queryParameters; PocketQuery.load({ container: container, data: data }); } $('.pocketquery-result').on('change', '#select-language', function() { var queryParameters = { Language: $('#select-language').val() }; reload('Countries', queryParameters); }); $('.pocketquery-result').on('change', '#select-country', function() { var queryParameters = { Language: $('#select-language').val(), Country: $('#select-country').val() }; reload('CountryByNameAndLanguage', queryParameters); }); }()); CountriesSelect (used by Countries query) #set($pqType = "Country") #set($pqInitialValue = "Sweden") $PocketQuery.template("FormTemplate") // trigger a change manually every time the query was loaded, so the // listener for the third query will trigger as if the select box were // changed by hand $("#select-country").change(); In my Confluence page I simply have a single macro for the Languages query. It looks like in . I know this looks quite complex, but once you got the gist of how templating with PocketQuery works, I think you will understand. Let me know if this helps and if you have further questions. Regards, Felix

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Populate cascading select lists using SQL database values

Dan Bishop_n900hd

Hi,

I have limited experience with templating and SQL so looking for some guidance.

I'm attempting to populate cascading select lists using values from an MS SQL database.

I'm querying two tables. The first table lists the software platform name, i.e Windows Server and the second table lists the version, i.e. 2008 R2.

Using my query and the code below, I've been able to successfully populate the "Platform" and "Version" select lists, but my Platform list has repeating values. How do I specify a one to many relationship in my template so only one instance of the platform is listed?

Example:

image2016-8-31 10:39:41.png

Also, there's no dependency between the two lists. (no cascading functionality) I'd like to be able to select a platform and only have the versions associated with that platform listed in the "Version" select list.

&lt;form id="my-pocketquery-form" class="aui"&gt;
    &lt;div class="field-group"&gt;
        &lt;label for="select-platform"&gt;Choose Platform:&lt;/label&gt;
        &lt;select class="select" id="select-platform" name="select-platform"&gt;
            #foreach ($platform in $result)
                &lt;option value="$platform.platform" #if($platform.platform == '.NET Framework')selected="selected"#end&gt;$platform.platform&lt;/option&gt;
            #end
        &lt;/select&gt; &lt;br&gt;
				        &lt;label for="select-version"&gt;Choose Version:&lt;/label&gt;
        &lt;select class="select" id="select-version" name="select-version"&gt;
            #foreach ($version in $result)
                &lt;option value="$version.version" #if($version.version == '1')selected="selected"#end&gt;$version.version&lt;/option&gt;
            #end
        &lt;/select&gt;
    &lt;/div&gt;
&lt;/form&gt;
$PocketQuery.renderPocketQueryMacro("Result_Query", {
    'page': $page,
    'parameters': {'Platform':'.NET Framework'},
		'parameters': {'Version':'1'},
    'dynamicload': true
})
&lt;script&gt;
jQuery('#my-pocketquery-form select').change(function() {
    var container = jQuery('.pocketquery-dynamic-load[data-query="Result_Query"]');
    var dataIndex = container.data('index');
    var data = PocketQuery.getDynamicLoadData(dataIndex);
    data.queryParameters = { VersionPlatform: jQuery('#select-platform').val() };
		data.queryParameters = { VersionPlatform: jQuery('#select-version').val() };
    PocketQuery.load({
        container: container,
        data: data
    });
});
&lt;/script&gt;
$PocketQuery.template("default")

Thanks,

Dan

image2016-8-31 10:39:41.png

image2016-9-10 12:8:40.png

image2016-9-10 12:11:29.png

screen-pq.gif

Accepted answers

Felix Grund (Scandio)

Hi Dan,

I tried to reproduce your issue with the MySQL Sample World DB:

image2016-9-10 12:8:40.png

It should come very close to your scenario. I did it this way:

select box for distinct languages
select box for countries that changes when language in (1) is changed
main table that queries the two tables with country and language as parameters

I have 3 queries:

1) Languages:

SELECT DISTINCT Language
FROM CountryLanguage
ORDER BY Language ASC

2) Countries:

SELECT Name as Country
FROM Country, CountryLanguage
WHERE Country.Code = CountryLanguage.CountryCode
AND Language = :Language
ORDER BY Name ASC

3) CountryByNameAndLanguage

SELECT Name, Language
FROM Country, CountryLanguage
WHERE Country.Code = CountryLanguage.CountryCode
AND Name = :Country
AND Language = :Language

Now comes the tricky part. All three macros in one page or one macro renders other macros from within its template? There are different ways you can achieve what you want. Each requires programming with Velocity and JS.

I used this approach: the template for the Languages query is the master and renders the two other queries. Also, I created a separate FormTemplate that is used by Languages AND Countries so we have no duplicate code. This template is included in the other templates with $PocketQuery.template("FormTemplate"). Before it is included, parameters must be set. See my following examples. The query CountryByNameAndLanguage uses the default template.

These are my templates...

FormTemplate (not used directly by any query, but included with $PocketQuery.template)

#set($pqTypeLower = $pqType.toLowerCase())
&lt;form id="form-$pqTypeLower" class="aui"&gt;
    &lt;div class="field-group"&gt;
        &lt;label for="select-$pqTypeLower"&gt;Choose $pqType:&lt;/label&gt;
        &lt;select class="select" id="select-$pqTypeLower" name="select-$pqTypeLower"&gt;
            #foreach ($row in $result)
                &lt;option value="$row.get($pqType)" #if($row.get($pqType) == $queryParameters.get($pqType) || $row.get($pqType) == $pqInitialValue)selected="selected"#end&gt;$row.get($pqType)&lt;/option&gt;
            #end
        &lt;/select&gt;
    &lt;/div&gt;
&lt;/form&gt;

LanguagesSelect (used by Languages query)

#set($pqType = "Language")
#set($pqInitialValue = "Swedish")
$PocketQuery.template("FormTemplate")
$PocketQuery.renderPocketQueryMacro("Countries", {
    "page": $page,
    "parameters": {"Language":"Swedish"},
    "dynamicload": "true"
})
$PocketQuery.renderPocketQueryMacro("CountryByNameAndLanguage", {
    "page": $page,
    "parameters": {"Country":"Sweden", "Language":"Swedish"},
    "dynamicload": "true"
})
&lt;script&gt;
(function() {
	function reload(queryName, queryParameters) {
		var container = $('.pocketquery-dynamic-load[data-query="'+queryName+'"]');
		var dataIndex = container.data('index');
		var data = PocketQuery.getDynamicLoadData(dataIndex);
		data.queryParameters = queryParameters;
		PocketQuery.load({
				container: container,
				data: data
		});
	}
	
	$('.pocketquery-result').on('change', '#select-language', function() {
		var queryParameters = { Language: $('#select-language').val() };
		reload('Countries', queryParameters);
	});
	
	$('.pocketquery-result').on('change', '#select-country', function() {
		var queryParameters = { Language: $('#select-language').val(), Country: $('#select-country').val() };
		reload('CountryByNameAndLanguage', queryParameters);
	});
}());
&lt;/script&gt;

CountriesSelect (used by Countries query)

#set($pqType = "Country")
#set($pqInitialValue = "Sweden")
$PocketQuery.template("FormTemplate")
 
&lt;script&gt;
// trigger a change manually every time the query was loaded, so the
// listener for the third query will trigger as if the select box were
// changed by hand
$("#select-country").change();
&lt;/script&gt;

In my Confluence page I simply have a single macro for the Languages query. It looks like in .

I know this looks quite complex, but once you got the gist of how templating with PocketQuery works, I think you will understand.

Let me know if this helps and if you have further questions.

Regards, Felix

image2016-9-10 11:52:56.png

All comments

Carla Roocks (Scandio)

Hi Dan,

Thank you for using PocketQuery!

Last year, Felix shared a beautiful minimal example for cascading results from PocketQuery queries. Maybe have a look at that: https://answers.atlassian.com/questions/32527656/answers/32966960

It works with two seperate, nested queries and I think, this is what you need here. You can use a "SELECT DISTINCT" query as outer query to get the platform information and get rid of the multiple entries. Then, using the inner query, you can populate the second select field with the corresponding versions. The "dynamic load" option should also come in handy.

I hope this helps. Let me know if you have further questions!

Regards,
Carla

Dan Bishop_n900hd

Hi Carla,

Thanks for your response! I went through your provided example and couldn't get it to work for some reason. sad

I was able to resolve the multiple platform entries by removing the "Version" component from my template + query. Adding DISTINCT in my SELECT query does not make a difference. So I'm able to query the platform successfully, and output to a default table. I just need to figure out how to add the version to a second select element and have it output to the same default table as the platform.

I'll look it over again this weekend to try and add the Version requirement back without breaking anything!

FYI, I've been using this example as a guide: https://answers.atlassian.com/questions/32997105

Question: Using the above approach, would I need to add another container object for the version parameter and add a second renderPocketQueryMacro method to my template?

Also, the platform and version are on two separate tables in the database. Not sure if that makes a difference, as I noticed that in the example you provided the query results were coming from a single table.

Thanks again!

Dan

Felix Grund (Scandio)

Hi Dan,

If I understand you right, you have the following scenario:

one table with platforms
one table with versions
for each table you want a select list
the versions select list should be populated depending on what you select in the platforms select list
the main result table should be changed each time one of the two select lists are changed

If my assumptions are correct, I would use a three-query approach:

Query distinct platform names
Query versions by platform
Query data by platform and version

The queries (1) and (2) should each have a form with one select list, similar to the template you wrote – but one form for each. The template of (1) should have a JS snippet that changes the select list content of (2) and the main result (3) upon change. The template of (2) should only change the main result (3) upon change. You'll have to write your JS in a clever way such that you don't have code redundancies.

I'm available only occasionally these days, but I can definitely help you further with this if you can't make it on your own. In that case it would be great if you provided your SQL structures in some way.

Regards, Felix

Dan Bishop_n900hd

Hi Felix,

Yes, your assumptions are correct.

I've been able to duplicate what I did for the platform select list with the versions listing, and each select list produces an independent distinct list of values.

Unfortunately I've been unable to create the dependency between the two select lists (Query 1 and 2) and output both to a single default table (Query 3). My knowledge of JS is severely limited, especially for a snippet this advanced.

Here's a breakdown of what I have so far, hoping you can add to it or provide direction on next steps.

SELECT Platform
FROM Standards
ORDER BY Platform ASC;

## @param emptymsg:The query returned no result!!
&lt;style&gt;
.pocketquery-view-container &gt; .aui-message.error { display: none; }
&lt;/style&gt;
&lt;form id="my-pocketquery-form" class="aui"&gt;
    &lt;div class="field-group"&gt;
        &lt;label for="select-platform"&gt;Choose Platform:&lt;/label&gt;
        &lt;select class="select" id="select-platform" name="select-platform"&gt;
            #foreach ($platform in $result)
                &lt;option value="$platform.Platform" #if($platform.Platform == 'Windows Server')selected="selected"#end&gt;$platform.Platform&lt;/option&gt;
            #end
        &lt;/select&gt;
    &lt;/div&gt;
&lt;/form&gt;
$PocketQuery.renderPocketQueryMacro("Result_Query", {
    "page": $page,
    "parameters": $queryParameters,
    "dynamicload": "true"
})
&lt;script&gt;
jQuery('#my-pocketquery-form select').change(function() {
    var container = jQuery('.pocketquery-dynamic-load[data-query="Result_Query"]');
    var dataIndex = container.data('index');
    var data = PocketQuery.getDynamicLoadData(dataIndex);
      data.queryParameters = { platform: jQuery('#select-platform').val() };       
    PocketQuery.load({
        container: container,
        data: data
    });
});

&lt;/script&gt;

SELECT DISTINCT Version
FROM Versions
ORDER BY Version ASC;

## @param emptymsg:The query returned no result!!
&lt;style&gt;
.pocketquery-view-container &gt; .aui-message.error { display: none; }
&lt;/style&gt;
&lt;form id="my-pocketquery-form" class="aui"&gt;
    &lt;div class="field-group"&gt;
        &lt;label for="select-version"&gt;Choose Version:&lt;/label&gt;
        &lt;select class="select" id="select-version" name="select-version"&gt;
            #foreach ($version in $result)
                &lt;option value="$version.Version" #if($version.Version == 'NT')selected="selected"#end&gt;$version.Version&lt;/option&gt;
            #end
        &lt;/select&gt;
    &lt;/div&gt;
&lt;/form&gt;
$PocketQuery.renderPocketQueryMacro("Test_Result_Query", {
    "page": $page,
    "parameters": $queryParameters,
    "dynamicload": "true"
})
&lt;script&gt;
jQuery('#my-pocketquery-form select').change(function() {
    var container = jQuery('.pocketquery-dynamic-load[data-query="Test_Result_Query"]');
    var dataIndex = container.data('index');
    var data = PocketQuery.getDynamicLoadData(dataIndex);
      data.queryParameters = { version: jQuery('#select-version').val() };       
    PocketQuery.load({
        container: container,
        data: data
    });
});

&lt;/script&gt;

SELECT Platform, Version, DeployDate, RetireDate
FROM INNER JOIN Versions ON Standards.[S_ID] = Versions.[V_ID] 
Where Platform = :platform AND Version = :version;

This query uses the default template.

I've been looking at this link to try and figure it out myself, but no luck...

http://stackoverflow.com/questions/10570904/use-jquery-to-change-a-second-select-list-based-on-the-first-select-list-option

Thanks,

Dan

Felix Grund (Scandio)

Hi Dan,

I tried to reproduce your issue with the MySQL Sample World DB:

image2016-9-10 12:8:40.png

It should come very close to your scenario. I did it this way:

select box for distinct languages
select box for countries that changes when language in (1) is changed
main table that queries the two tables with country and language as parameters

I have 3 queries:

1) Languages:

SELECT DISTINCT Language
FROM CountryLanguage
ORDER BY Language ASC

2) Countries:

SELECT Name as Country
FROM Country, CountryLanguage
WHERE Country.Code = CountryLanguage.CountryCode
AND Language = :Language
ORDER BY Name ASC

3) CountryByNameAndLanguage

SELECT Name, Language
FROM Country, CountryLanguage
WHERE Country.Code = CountryLanguage.CountryCode
AND Name = :Country
AND Language = :Language

These are my templates...

FormTemplate (not used directly by any query, but included with $PocketQuery.template)

#set($pqTypeLower = $pqType.toLowerCase())
&lt;form id="form-$pqTypeLower" class="aui"&gt;
    &lt;div class="field-group"&gt;
        &lt;label for="select-$pqTypeLower"&gt;Choose $pqType:&lt;/label&gt;
        &lt;select class="select" id="select-$pqTypeLower" name="select-$pqTypeLower"&gt;
            #foreach ($row in $result)
                &lt;option value="$row.get($pqType)" #if($row.get($pqType) == $queryParameters.get($pqType) || $row.get($pqType) == $pqInitialValue)selected="selected"#end&gt;$row.get($pqType)&lt;/option&gt;
            #end
        &lt;/select&gt;
    &lt;/div&gt;
&lt;/form&gt;

LanguagesSelect (used by Languages query)

#set($pqType = "Language")
#set($pqInitialValue = "Swedish")
$PocketQuery.template("FormTemplate")
$PocketQuery.renderPocketQueryMacro("Countries", {
    "page": $page,
    "parameters": {"Language":"Swedish"},
    "dynamicload": "true"
})
$PocketQuery.renderPocketQueryMacro("CountryByNameAndLanguage", {
    "page": $page,
    "parameters": {"Country":"Sweden", "Language":"Swedish"},
    "dynamicload": "true"
})
&lt;script&gt;
(function() {
	function reload(queryName, queryParameters) {
		var container = $('.pocketquery-dynamic-load[data-query="'+queryName+'"]');
		var dataIndex = container.data('index');
		var data = PocketQuery.getDynamicLoadData(dataIndex);
		data.queryParameters = queryParameters;
		PocketQuery.load({
				container: container,
				data: data
		});
	}
	
	$('.pocketquery-result').on('change', '#select-language', function() {
		var queryParameters = { Language: $('#select-language').val() };
		reload('Countries', queryParameters);
	});
	
	$('.pocketquery-result').on('change', '#select-country', function() {
		var queryParameters = { Language: $('#select-language').val(), Country: $('#select-country').val() };
		reload('CountryByNameAndLanguage', queryParameters);
	});
}());
&lt;/script&gt;

CountriesSelect (used by Countries query)

#set($pqType = "Country")
#set($pqInitialValue = "Sweden")
$PocketQuery.template("FormTemplate")
 
&lt;script&gt;
// trigger a change manually every time the query was loaded, so the
// listener for the third query will trigger as if the select box were
// changed by hand
$("#select-country").change();
&lt;/script&gt;

In my Confluence page I simply have a single macro for the Languages query. It looks like in .

I know this looks quite complex, but once you got the gist of how templating with PocketQuery works, I think you will understand.

Let me know if this helps and if you have further questions.

Regards, Felix

image2016-9-10 11:52:56.png

Dan Bishop_n900hd

It works!

Felix, I cannot thank you enough for your help on this. Your support for PocketQuery is always top-notch. This community is lucky to have you!!

Quick question about the default "selected" option. In your example above you have "Sweden" and "Swedish" selected as the default options. Am I able to set this as "Select" for both as default without the result returning an error on page load? I believe there would be an error on page load because "Select" isn't an actual option in my tables.

Thanks again,

Dan

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

pocketquery