Query depending on multiple select boxes

We recently had a support request with a scenario where a query result depended on the values of two different select boxes. Furthermore, one of the two select boxes was dependent on the chosen value of the other select box. I reproduced this scenario with my sample World SQL database as follows:

The idea here is this: I select a continent (left select box) and the list of countries (right select box) will update by executing another PocketQuery in the background. Then, when I click on "Update", the main query will be executed with the continent and country in the above select boxes. The result will display the country's continent, country, and population.

As always, this is for demonstration purposes. I acknowledge that this maybe doesn't make too much sense from a content perspective. At the bottom of this page is a GIF that shows how this works.

This scenario is kind of a more advanced version of Dynamic dropdown menu for query parameters. If you haven't seen this example before, I recommend you check it out first.

Setup

The way this example works is with with following entities:

3 Queries:
- OuterQuery: renders the form at the top and the left select box with all continents from the DB. The template of this query renders the other 2 queries dynamically.
- NestedQuery1: renders the second select box with countries. The values depend on a `Continent` query parameter that is passed from the first select box.
- NestedQuery2: renders the actual result based on the parameters `Continent` and `Country` that are passed from the two checkboxes.
2 Templates
- OuterTemplate: template for OuterQuery that will render the form and the two other queries
- NestedTemplate1: template for NestedQuery1 that renders the select box for country names
- (for NestedQuery2 we just use the default template)

Here is the code for this scenario. This is definitely a bit more complex. I tried to explain everything with comments in the code.

Queries

OuterQuery

CODE

SELECT DISTINCT Continent
FROM country

This is simple: we just select all continents from our table `Country`

NestedQuery1

CODE

SELECT Name as Country
FROM country
WHERE Continent = :Continent

Also quite simple: we select our countries by continent.

NestedQuery2

CODE

SELECT Continent, Name as Country, Population
FROM country
WHERE Continent LIKE :Continent
AND Name = :Country

I think this is still quite simple. So it seems that actually all our queries are quite simple. This selects the continent, country name, and population based on the parameters Continent and Country.

Templates

Now here's the more complex part. We'll need to render multiple other queries dynamically from a template using JavaScript. This has been described before in Template For Dynamic Parameter Change and Dynamic dropdown menu for query parameters. Let's do it again here.

OuterTemplate

This is the template for OuterQuery:

CODE

## We need the query name and the query key for both nested queries.
## The query key can be found in the query edior below the title.
#set($firstNestedQuery = "NestedQuery1")
#set($firstNestedQueryKey = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")
#set($secondNestedQuery = "NestedQuery2")
#set($secondNestedQueryKey = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx")
 
<style>
## Just make our select boxes a fixed width
#select-continent, #select-country { width: 230px; }
</style>
  
## This is the form with the two select boxes
<form method="get" class="aui pq-dynamic-parameter-form" action="">
    <select class="select" id="select-continent">
        ## This will render all the continents from our OuterQuery into the select box
        ## We give it a default value of "Europe"
        #foreach($row in $result)
            <option #if($row.Continent == "Europe")selected="selected"#end
                value="$row.Continent">$row.Continent</option>
        #end
    </select>
    ## Here we render our NestedQuery1 using the renderPocketQueryMacro helper
    ## We pass on the default value of europe
    $PocketQuery.renderPocketQueryMacro($firstNestedQueryKey, {
        "page": $page,
        "parameters": { "Continent":"Europe" },
        "dynamicload": "true"
    })
    ## Clicking this button will update the table
    ## (by taking the values of the select boxes and re-rendering NestedQuery2)
    <button type="submit" id="pq-update">Update</button>
</form>
  
## This query will render the table below, taking Continent and Country
## from the select boxes above.
## We pass on the default values we chose above.
$PocketQuery.renderPocketQueryMacro($secondNestedQueryKey, {
  "page": $page,
  "parameters": { "Continent":"Europe", "Country":"Sweden" },
  "dynamicload": "true"
})
  
<script>
  
// Whenever the continent select value changes, we want to execute this!
jQuery("#select-continent").change(function() {
    // This has been described before in the linked articles: we dynamically
    // reload out Countries based on the given Continent
    var container = jQuery('.pocketquery-dynamic-load[data-query="$firstNestedQuery"]');
    var data = PocketQuery.getDynamicLoadData(container);
         
    data.queryParameters = { Continent: jQuery(this).val() };
    PocketQuery.load({
        container: container,
        data: data
    });
});
  
// Whenever we click on the Update button, we want to update our NestedQuery2.
// We extract the Continent and Country from our two select boxes.
jQuery("#pq-update").click(function(e) {
    var container = jQuery('.pocketquery-dynamic-load[data-query="$secondNestedQuery"]');
    var data = PocketQuery.getDynamicLoadData(container);

    e.preventDefault();
    data.queryParameters = {
        Continent: jQuery("#select-continent").val(),
        Country: jQuery("#select-country").val()
    };
    PocketQuery.load({
        container: container,
        data: data
    });
});
  
</script>

NestedTemplate1

This template needs to be set for both NestedQuery1 and NestedQuery2:

CODE

## Render a select box with an option for each country. We start with
## "Sweden" as the default.
<select class="select" id="select-country">
    #foreach($row in $result)
       <option #if($row.Country == "Sweden")selected="selected"#end
        value="$row.Country">$row.Country</option>
    #end
</select>

The result in action!