Populate cascading select lists using SQL database values

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.

 

 

<form id="my-pocketquery-form" class="aui">
    <div class="field-group">
        <label for="select-platform">Choose Platform:</label>
        <select class="select" id="select-platform" name="select-platform">
            #foreach ($platform in $result)
                <option value="$platform.platform" #if($platform.platform == '.NET Framework')selected="selected"#end>$platform.platform</option>
            #end
        </select> <br>
				        <label for="select-version">Choose Version:</label>
        <select class="select" id="select-version" name="select-version">
            #foreach ($version in $result)
                <option value="$version.version" #if($version.version == '1')selected="selected"#end>$version.version</option>
            #end
        </select>
    </div>
</form>
$PocketQuery.renderPocketQueryMacro("Result_Query", {
    'page': $page,
    'parameters': {'Platform':'.NET Framework'},
		'parameters': {'Version':'1'},
    'dynamicload': true
})
<script>
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
    });
});
</script>
$PocketQuery.template("default")

 

 

Thanks,

 

Dan

3 answers

1 accepted

This widget could not be displayed.

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:

  1. select box for distinct languages
  2. select box for countries that changes when language in (1) is changed
  3. 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())
<form id="form-$pqTypeLower" class="aui">
    <div class="field-group">
        <label for="select-$pqTypeLower">Choose $pqType:</label>
        <select class="select" id="select-$pqTypeLower" name="select-$pqTypeLower">
            #foreach ($row in $result)
                <option value="$row.get($pqType)" #if($row.get($pqType) == $queryParameters.get($pqType) || $row.get($pqType) == $pqInitialValue)selected="selected"#end>$row.get($pqType)</option>
            #end
        </select>
    </div>
</form>

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"
})
<script>
(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);
	});
}());
</script>

CountriesSelect (used by Countries query)

#set($pqType = "Country")
#set($pqInitialValue = "Sweden")
$PocketQuery.template("FormTemplate")
 
<script>
// 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();
</script>

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

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

This widget could not be displayed.

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

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

 

 

This widget could not be displayed.

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:

  1. Query distinct platform names
  2. Query versions by platform
  3. 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

 

 

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!!
<style>
.pocketquery-view-container > .aui-message.error { display: none; }
</style>
<form id="my-pocketquery-form" class="aui">
    <div class="field-group">
        <label for="select-platform">Choose Platform:</label>
        <select class="select" id="select-platform" name="select-platform">
            #foreach ($platform in $result)
                <option value="$platform.Platform" #if($platform.Platform == 'Windows Server')selected="selected"#end>$platform.Platform</option>
            #end
        </select>
    </div>
</form>
$PocketQuery.renderPocketQueryMacro("Result_Query", {
    "page": $page,
    "parameters": $queryParameters,
    "dynamicload": "true"
})
<script>
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
    });
});

</script>
SELECT DISTINCT Version
FROM Versions
ORDER BY Version ASC;
## @param emptymsg:The query returned no result!!
<style>
.pocketquery-view-container > .aui-message.error { display: none; }
</style>
<form id="my-pocketquery-form" class="aui">
    <div class="field-group">
        <label for="select-version">Choose Version:</label>
        <select class="select" id="select-version" name="select-version">
            #foreach ($version in $result)
                <option value="$version.Version" #if($version.Version == 'NT')selected="selected"#end>$version.Version</option>
            #end
        </select>
    </div>
</form>
$PocketQuery.renderPocketQueryMacro("Test_Result_Query", {
    "page": $page,
    "parameters": $queryParameters,
    "dynamicload": "true"
})
<script>
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
    });
});

</script>
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

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

606 views 8 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